﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Win32;
using System.Reflection;
using System.Xml;
using System.Xml.Xsl;
using System.Xml.XPath;
using System.IO;
using System.Data;
using System.Globalization;
using System.Threading;
using System.Windows.Controls;
using OutlookCalendar.Model;


namespace AnonsaiWPF
{
    class Office
    {
        public static MainWindow mw;

        public static string AppPath
        {
            get
            {
                return System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase).Substring(6);
            }
        }

        static public string getOfficeVersion()
        {
            string version = "";
            RegistryKey RK = Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Office\\14.0"); //office 2000
            if (RK != null)
            {
                version = "2010";
            }
            else
            {
                RK = Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Office\\12.0"); //office 2003
                if (RK != null)
                {
                    version = "2007";
                }
                else
                {
                    RK = Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Office\\11.0"); //office 2007
                    if (RK != null)
                    {
                        version = "2003";
                    }
                    else
                    {
                        RK = Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Office\\9.0"); //office 2010
                        if (RK != null)
                        {
                            version = "2010";
                        }
                    }
                }
            }
            return version;
        }

        public static bool checkMSOfficeWord()
        {
            bool rez = false;
            string key = "";
            if (getOfficeVersion().Equals("2000"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\9.0\\Word";
            }
            if (getOfficeVersion().Equals("2003"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\11.0\\Word";
            }
            if (getOfficeVersion().Equals("2007"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\12.0\\Word";
            }
            if (getOfficeVersion().Equals("2010"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\14.0\\Word";
            }
            RegistryKey RK = Registry.LocalMachine.OpenSubKey(key);
            if (RK != null)
            {
                rez = true;
            }
            return rez;
        }

        public static bool checkMSOfficeExcel()
        {
            bool rez = false;
            string key = "";
            if (getOfficeVersion().Equals("2000"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\9.0\\Excel";
            }
            if (getOfficeVersion().Equals("2003"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\11.0\\Excel";
            }
            if (getOfficeVersion().Equals("2007"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\12.0\\Excel";
            }
            if (getOfficeVersion().Equals("2010"))
            {
                key = "SOFTWARE\\Microsoft\\Office\\14.0\\Excel";
            }
            RegistryKey RK = Registry.LocalMachine.OpenSubKey(key);
            if (RK != null)
            {
                rez = true;
            }
            return rez;
        }

        public static void exportToExcel(string filename,DataGrid dt)
        {
            if (getOfficeVersion().Equals("2000"))
            {
                exportToExcelOld(filename,dt);
            }
            else
            {
                exportToExcelNewer(filename,dt);
            }
        }

        public static void exportToWord(string filename, DataGrid dt)
        {
            if (getOfficeVersion().Equals("2000"))
            {
                exportToWordOld(filename, dt);
            }
            else
            {
                exportToWordNewer(filename, dt);
            }
        }

        public static void exportTinklelisToExcel(string filename, DataTable dt, DateTime dtn)
        {
            if (getOfficeVersion().Equals("2000"))
            {
                exportTinklelisToExcelOld(filename, dt,dtn);
            }
            else
            {
                exportTinklelisToExcelNewer(filename, dt,dtn);
            }
        }

        static void exportTinklelisToExcelOld(string filename, DataTable dt, DateTime dtn)
        {
        }

        static void exportTinklelisToExcelNewer(string filename, DataTable dt, DateTime dtn)
        {
            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel._Workbook oWBold = null;
            Microsoft.Office.Interop.Excel._Worksheet oSheetold = null;

            Microsoft.Office.Interop.Excel.Application oXLold = new Microsoft.Office.Interop.Excel.Application();
            oXLold.Visible = false;


            oWBold = (Microsoft.Office.Interop.Excel._Workbook)(oXLold.Workbooks.Open(AppPath + "\\templateTinklelis.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
            oSheetold = (Microsoft.Office.Interop.Excel._Worksheet)oWBold.ActiveSheet;
            string weekday = "";
            if (dtn.DayOfWeek==DayOfWeek.Monday){
                weekday = "Pirmadienis";
            }
            else if (dtn.DayOfWeek==DayOfWeek.Tuesday){
                weekday = "Antradienis";
            }
            else if (dtn.DayOfWeek==DayOfWeek.Wednesday){
                weekday = "Trečiadienis";
            }
            else if (dtn.DayOfWeek==DayOfWeek.Thursday){
                weekday = "Ketvirtadienis";
            }
            else if (dtn.DayOfWeek==DayOfWeek.Friday){
                weekday = "Penktadienis";
            }
            else if (dtn.DayOfWeek==DayOfWeek.Saturday){
                weekday = "Šeštadienis";
            }
            else if (dtn.DayOfWeek==DayOfWeek.Sunday){
                weekday = "Sekmadienis";
            }
            string month = dtn.Month.ToString();
            string day = dtn.Day.ToString();
            if (month.Length < 2) { month = "0" + month; }
            if (day.Length < 2) { day = "0" + day; }
            oSheetold.get_Range("C1", "C1").Value = "Anonsai " + weekday + " " + month + " " + day;

            string anonsTime = "";
            int ix = 0;
            int boundstart = 0;
            int boundend = 0;
            int cnt = 0;
            int e = 0;
            for (int i = 0; i < dt.Rows.Count-1; i++)
            {
                //DataRowView li = (DataRowView)dt.Items[i];
                string pre = "";
                if (i == 0)
                {
                    anonsTime = dt.Rows[i]["StartTime"].ToString();
                    ix++;
                    oSheetold.get_Range("A" + (e + 3).ToString(), "A" + (e + 3).ToString()).Value = ix.ToString();
                    oSheetold.get_Range("C" + (e + 3).ToString(), "C" + (e + 3).ToString()).Font.Bold = true;
                    oSheetold.get_Range("C" + (e + 3).ToString(), "D" + (e + 3).ToString()).Interior.Color = System.Drawing.Color.Yellow;
                    boundstart = e+ 3;
                    cnt = 0;
                }else if (dt.Rows[i-1]["StartTime"].ToString().Equals("")){
                   anonsTime = dt.Rows[i]["StartTime"].ToString();
                   ix++;
                   oSheetold.get_Range("A" + (e + 3).ToString(), "A" + (e + 3).ToString()).Value = ix.ToString();
                   oSheetold.get_Range("C" + (e + 3).ToString(), "C" + (e + 3).ToString()).Font.Bold = true;
                   oSheetold.get_Range("C" + (e + 3).ToString(), "D" + (e + 3).ToString()).Interior.Color = System.Drawing.Color.Yellow;
                   boundstart = e + 3;
                   cnt = 0;
                }
                pre=dt.Rows[i+1]["StartTime"].ToString();
                if (pre.Equals("")) { 
                    anonsTime = ""; boundend = e+3;
                    oSheetold.get_Range("C" + (e + 2).ToString(), "C" + (e + 2).ToString()).Font.Bold = true;
                    oSheetold.get_Range("C" + (e + 2).ToString(), "D" + (e + 2).ToString()).Interior.Color = System.Drawing.Color.Yellow;
                }
                if (!pre.Equals("") && !anonsTime.Equals(""))
                {
                    cnt++;
                    oSheetold.get_Range("B" + (e + 3).ToString(), "B" + (e + 3).ToString()).Value = anonsTime;
                    string anonsName = dt.Rows[i]["Name"].ToString();
                    oSheetold.get_Range("C" + (e + 3).ToString(), "C" + (e+ 3).ToString()).Value = anonsName;
                    string anonsDuration = dt.Rows[i]["Duration"].ToString();
                    oSheetold.get_Range("D" + (e + 3).ToString(), "D" + (e + 3).ToString()).Value = anonsDuration;
                    oSheetold.get_Range("E" + (e + 3).ToString(), "E" + (e + 3).ToString()).Value = cnt.ToString();
                }
                if (dt.Rows[i]["Name"].ToString().Equals("") && !(dt.Rows[i]["Duration"].ToString().Equals("")))
                 {
                    string anonsDuration = dt.Rows[i]["Duration"].ToString();
                    oSheetold.get_Range("D" + (e + 3).ToString(), "D" + (e + 3).ToString()).Value = anonsDuration;
                    oSheetold.get_Range("D" + (e + 3).ToString(), "D" + (e + 3).ToString()).Font.Bold = true;
                    e--;
                   // oSheetold.get_Range("A" + (e + 3).ToString(), "A" + (e + 3).ToString()).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = 2;
                    Microsoft.Office.Interop.Excel.Range range = oSheetold.get_Range("B" + boundstart.ToString(), "E" + boundend.ToString());
                    Microsoft.Office.Interop.Excel.Border border = range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical];
                    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    border.Color = System.Drawing.Color.Black;

                    border = range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal];
                    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    border.Color = System.Drawing.Color.Black;

                    border = range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft];
                    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    border.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
                    border.Color = System.Drawing.Color.Black;

                    border = range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight];
                    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    border.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
                    border.Color = System.Drawing.Color.Black;

                    border = range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom];
                    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    border.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
                    border.Color = System.Drawing.Color.Black;

                    border = range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop];
                    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    border.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
                    border.Color = System.Drawing.Color.Black;
                }
                e++;
            }

            oWBold.SaveAs(filename);
            oXLold.Quit();
            oSheetold = null;
            oWBold = null;
            oXLold = null;

            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
      
        }

        public static void exportPlanasToExcel(string filename, DataGrid dg, DataTable dt, DateTime dtn)
        {
            if (getOfficeVersion().Equals("2000"))
            {
                exportPlanasToExcelOld(filename,dg, dt,dtn);
            }
            else
            {
                exportPlanasToExcelNewer(filename,dg, dt,dtn);
            }
        }

        static void exportPlanasToExcelOld(string filename, DataGrid dg, DataTable dt, DateTime dtn)
        {
        }

        static void exportPlanasToExcelNewer(string filename,DataGrid dg, DataTable dt,DateTime dtn)
        {
            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel._Workbook oWBold = null;
            Microsoft.Office.Interop.Excel._Worksheet oSheetold = null;

            Microsoft.Office.Interop.Excel.Application oXLold = new Microsoft.Office.Interop.Excel.Application();
            oXLold.Visible = false;


            oWBold = (Microsoft.Office.Interop.Excel._Workbook)(oXLold.Workbooks.Open(AppPath + "\\templatePlanas.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true));
            oSheetold = (Microsoft.Office.Interop.Excel._Worksheet)oWBold.ActiveSheet;


            for (int i = 5; i < dg.Columns.Count; i++)
            {

                DataGridColumn textColumn = dg.Columns[i];
                string columnheader = textColumn.Header.ToString();
               // string columnheader = mw.GetHeader(dg, i);

                string Character = ((char)(65 + i)).ToString()+"2";
                oSheetold.get_Range(Character, Character).Value = columnheader;
                Character = ((char)(65 + i)).ToString() + "3";
                oSheetold.get_Range(Character, Character).Value = dt.Rows[0][i].ToString();
            }

            for (int i = 1; i < dt.Rows.Count; i++)
            {
                //DataRowView li = (DataRowView)dt.Items[i];
                //string anonsTime = dt.Rows[i]["AnonsTime"].ToString();
                string pr = dt.Rows[i]["prog"].ToString();
                oSheetold.get_Range("A" + (i + 4).ToString(), "A" + (i + 4).ToString()).Value = dt.Rows[i]["time"].ToString();
                oSheetold.get_Range("B" + (i + 4).ToString(), "B" + (i + 4).ToString()).Value = dt.Rows[i]["prog"].ToString();
                oSheetold.get_Range("C" + (i + 4).ToString(), "C" + (i + 4).ToString()).Value = dt.Rows[i]["rate"].ToString();
                oSheetold.get_Range("D" + (i + 4).ToString(), "D" + (i + 4).ToString()).Value = dt.Rows[i]["blockDuration"].ToString();
                for (int y = 5; y < dg.Columns.Count; y++)
                {
                    string Character = ((char)(65 + y)).ToString() + (i + 4).ToString();
                    string colname="an" + (y-5).ToString();
                    oSheetold.get_Range(Character, Character).Value = dt.Rows[i][colname].ToString();
                }
                if (i < dt.Rows.Count - 18) //((dt.Rows[i+2]["time"].ToString().Equals("") && dt.Rows[i+2]["prog"].ToString().Equals("") && dt.Rows[i+2]["rate"].ToString().Equals("") && dt.Rows[i+2]["blockDuration"].ToString().Equals("")))//(i < dt.Rows.Count - 18)
                {

                    Microsoft.Office.Interop.Excel.Range sourceRange = oSheetold.get_Range("A" + (i + 5).ToString(), "FN" + (i + 5).ToString());
                    Microsoft.Office.Interop.Excel.Range destinationRange = oSheetold.get_Range("A" + (i + 6).ToString(), "FN" + (i + 6).ToString());

                    sourceRange.Copy(Type.Missing);
                    Microsoft.Office.Interop.Excel.Range r = oSheetold.get_Range("A" + (i + 5).ToString(), "FN" + (i + 5).ToString()).EntireRow;
                    r.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown);
                    destinationRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                }
                if (dt.Rows[i]["time"].ToString().Equals("") && dt.Rows[i]["prog"].ToString().Equals("") && dt.Rows[i]["rate"].ToString().Equals("") && dt.Rows[i]["blockDuration"].ToString().Equals(""))
                {
                    break;
                }
            }

            oWBold.SaveAs(filename);
            oXLold.Quit();
            oSheetold = null;
            oWBold = null;
            oXLold = null;

            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;

        }

        static void exportToExcelOld(string filename, DataGrid dt)
        {
            /*  CultureInfo myCIintl = new CultureInfo("en-US");
              System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;

              oXLold = new Excel.ApplicationClass();
              oXLold.Visible = false;
              if (dbc.checkConnection())
              {

                  oWBold = (Excel._Workbook)(oXLold.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
                  oSheetold = (Excel._Worksheet)oWBold.ActiveSheet;

                  String e1 = oSheetold.get_Range("A1", "A1").Text.ToString();
                  String e2 = oSheetold.get_Range("A2", "A2").Text.ToString();
                  bool flag = true;
                  int y = 0;
                  int e = 3;
                  while (!e1.Equals("") || !e2.Equals(""))
                  {

                      y++;
                      e++;
                      bl.Campaign[y] = oSheetold.get_Range("A" + e.ToString(), "A" + e.ToString()).Text.ToString();
                      bl.Date[y] = oSheetold.get_Range("B" + e.ToString(), "B" + e.ToString()).Text.ToString();
                      bl.StartTime[y] = oSheetold.get_Range("C" + e.ToString(), "C" + e.ToString()).Text.ToString();
                      bl.Duration[y] = oSheetold.get_Range("D" + e.ToString(), "D" + e.ToString()).Text.ToString().Replace('\'', ' '); ;
                      bl.POS_BLOCK[y] = oSheetold.get_Range("E" + e.ToString(), "E" + e.ToString()).Text.ToString();
                      bl.Brand[y] = oSheetold.get_Range("F" + e.ToString(), "F" + e.ToString()).Text.ToString();
                      bl.Advertisers[y] = oSheetold.get_Range("G" + e.ToString(), "G" + e.ToString()).Text.ToString();
                      Form1.Advertisers = Form1.add_to_advertisers(Form1.Advertisers, bl.Advertisers[y]);
                      bl.Broadcast_Title[y] = oSheetold.get_Range("H" + e.ToString(), "H" + e.ToString()).Text.ToString();
                      bl.GRP_Ind4plus[y] = oSheetold.get_Range("I" + e.ToString(), "I" + e.ToString()).Text.ToString().Replace('\'', ' '); ;
                      bl.GRP_15_49[y] = oSheetold.get_Range("J" + e.ToString(), "J" + e.ToString()).Text.ToString().Replace('\'', ' '); ;
                      bl.GRP_w18_49[y] = oSheetold.get_Range("K" + e.ToString(), "K" + e.ToString()).Text.ToString().Replace('\'', ' '); ;
                      bl.QuaterRtg_Ind4plus[y] = oSheetold.get_Range("L" + e.ToString(), "L" + e.ToString()).Text.ToString();
                      bl.QuaterRtg_15_49[y] = oSheetold.get_Range("M" + y.ToString(), "M" + e.ToString()).Text.ToString();
                      bl.QuaterRtg_w18_49[y] = oSheetold.get_Range("N" + e.ToString(), "N" + e.ToString()).Text.ToString();
                      bl.POS_BLOCK_EDT[y] = "1";
                      bl.ilgis = y;
                      string sql = "insert into TNS_data values('" + bl.Date[y] + "','" + bl.StartTime[y] + "','" + bl.Campaign[y] + "','" + bl.Duration[y] + "','" + bl.POS_BLOCK[y] + "','" + bl.Brand[y] + "','" + bl.Advertisers[y] + "','" + bl.Broadcast_Title[y] + "','" + bl.GRP_Ind4plus[y] + "','" + bl.GRP_15_49[y] + "','" + bl.GRP_w18_49[y] + "','" + bl.QuaterRtg_Ind4plus[y] + "','" + bl.QuaterRtg_15_49[y] + "','" + bl.QuaterRtg_w18_49[y] + "','','','','','','','','','','','','False','False','" + bl.POS_BLOCK_EDT[y] + "')";
                      if ((!(bl.Date[y].Equals(""))) && (!(bl.Date[y].Equals("Date"))))
                      {
                          try
                          {
                              dbc.sqlExecute(sql);
                          }
                          catch (Exception exx) { }
                      }

                      e1 = oSheetold.get_Range("A" + e.ToString(), "A" + e.ToString()).Text.ToString();
                      e2 = oSheetold.get_Range("A" + (e + 1).ToString(), "A" + (e + 1).ToString()).Text.ToString();
                  }


              }
              else { MessageBox.Show("neprisijungiama prie DB!"); }

              oXLold.Quit();
              oSheetold = null;
              oWBold = null;
              oXLold = null;

              myCIintl = new CultureInfo("lt-LT");
              System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl; */
        }

        static void exportToExcelNewer(string filename, DataGrid dt)
        {
            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel._Workbook oWBold = null;
            Microsoft.Office.Interop.Excel._Worksheet oSheetold = null; 

            Microsoft.Office.Interop.Excel.Application oXLold = new Microsoft.Office.Interop.Excel.Application();
            oXLold.Visible = false;


            oWBold = (Microsoft.Office.Interop.Excel._Workbook)(oXLold.Workbooks.Open(AppPath+"\\template.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
            oSheetold = (Microsoft.Office.Interop.Excel._Worksheet)oWBold.ActiveSheet;


            oSheetold.get_Range("A1", "A1").Value = "Kasetes Nr.";
            oSheetold.get_Range("B1", "B1").Value = "Kompanija";
            oSheetold.get_Range("C1", "C1").Value = "Pavadinimas originalo kalba";
            oSheetold.get_Range("D1", "D1").Value = "Lietuviškas pavadinimas";
            oSheetold.get_Range("E1", "E1").Value = "Trukmė 1";
            oSheetold.get_Range("F1", "F1").Value = "Trukmė 2";
            oSheetold.get_Range("G1", "G1").Value = "TC trukmė";
            oSheetold.get_Range("H1", "H1").Value = "Pradzios TC";
            oSheetold.get_Range("I1", "I1").Value = "I Įsikirtimas";
            oSheetold.get_Range("J1", "J1").Value = "II Įsikirtimas";
            oSheetold.get_Range("K1", "K1").Value = "III Įsikirtimas";
            oSheetold.get_Range("L1", "L1").Value = "IV Įsikirtimas";
            oSheetold.get_Range("M1", "M1").Value = "V Įsikirtimas";
            oSheetold.get_Range("N1", "N1").Value = "VI Įsikirtimas";
            oSheetold.get_Range("O1", "O1").Value = "VII Įsikirtimas";
            oSheetold.get_Range("P1", "P1").Value = "VIII Įsikirtimas";
            oSheetold.get_Range("Q1", "Q1").Value = "IX Įsikirtimas";
            oSheetold.get_Range("R1", "R1").Value = "X Įsikirtimas";
            oSheetold.get_Range("S1", "S1").Value = "XI Įsikirtimas";
            oSheetold.get_Range("T1", "T1").Value = "XII Įsikirtimas";
            oSheetold.get_Range("U1", "U1").Value = "XIII Įsikirtimas";
            oSheetold.get_Range("V1", "V1").Value = "XIV Įsikirtimas";
            oSheetold.get_Range("W1", "W1").Value = "XV Įsikirtimas";
            oSheetold.get_Range("X1", "X1").Value = "Eteris 1";
            oSheetold.get_Range("Y1", "Y1").Value = "Eteris 2";
            oSheetold.get_Range("Z1", "Z1").Value = "Eteris 3";
            oSheetold.get_Range("AA1", "AA1").Value = "Eteris 4";
            oSheetold.get_Range("AB1", "AB1").Value = "Eteris 5";
            oSheetold.get_Range("AC1", "AC1").Value = "P raidė";
            oSheetold.get_Range("AD1", "AD1").Value = "Intarpai bėg#eil";
            oSheetold.get_Range("AE1", "AE1").Value = "Licencijos pradžia";
            oSheetold.get_Range("AF1", "AF1").Value = "Licencijos pabaiga";
            oSheetold.get_Range("AG1", "AG1").Value = "Žanras";
            oSheetold.get_Range("AH1", "AH1").Value = "Režisierius";
            oSheetold.get_Range("AI1", "AI1").Value = "Aktoriai";
            oSheetold.get_Range("AJ1", "AJ1").Value = "Šalis";
            oSheetold.get_Range("AK1", "AK1").Value = "Metai";
            oSheetold.get_Range("AL1", "AL1").Value = "Sezonas";
            oSheetold.get_Range("AM1", "AM1").Value = "Serija";
            oSheetold.get_Range("AN1", "AN1").Value = "Indeksas";
            for (int i = 0; i < dt.Items.Count-1; i++)
                {
                    DataRowView li = (DataRowView)dt.Items[i];
                    oSheetold.get_Range("A" + (i+2).ToString(), "A" + (i+2).ToString()).Value = li.Row.ItemArray.GetValue(0).ToString();
                    oSheetold.get_Range("B" + (i+2).ToString(), "B" + (i+2).ToString()).Value = li.Row.ItemArray.GetValue(1).ToString();
                    oSheetold.get_Range("C" + (i + 2).ToString(), "C" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(2).ToString();
                    oSheetold.get_Range("D" + (i + 2).ToString(), "D" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(3).ToString();
                    oSheetold.get_Range("E" + (i + 2).ToString(), "E" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(4).ToString();
                    oSheetold.get_Range("F" + (i + 2).ToString(), "F" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(5).ToString();
                    oSheetold.get_Range("G" + (i + 2).ToString(), "G" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(6).ToString();
                    oSheetold.get_Range("H" + (i + 2).ToString(), "H" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(7).ToString();
                    oSheetold.get_Range("I" + (i + 2).ToString(), "I" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(8).ToString();
                    oSheetold.get_Range("J" + (i + 2).ToString(), "J" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(9).ToString();
                    oSheetold.get_Range("K" + (i + 2).ToString(), "K" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(10).ToString();
                    oSheetold.get_Range("L" + (i + 2).ToString(), "L" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(11).ToString();
                    oSheetold.get_Range("M" + (i + 2).ToString(), "M" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(12).ToString();
                    oSheetold.get_Range("N" + (i + 2).ToString(), "N" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(13).ToString();
                    oSheetold.get_Range("O" + (i + 2).ToString(), "O" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(14).ToString();
                    oSheetold.get_Range("P" + (i + 2).ToString(), "P" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(15).ToString();
                    oSheetold.get_Range("Q" + (i + 2).ToString(), "Q" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(16).ToString();
                    oSheetold.get_Range("R" + (i + 2).ToString(), "R" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(17).ToString();
                    oSheetold.get_Range("S" + (i + 2).ToString(), "S" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(18).ToString();
                    oSheetold.get_Range("T" + (i + 2).ToString(), "T" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(19).ToString();
                    oSheetold.get_Range("U" + (i + 2).ToString(), "U" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(20).ToString();
                    oSheetold.get_Range("V" + (i + 2).ToString(), "V" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(21).ToString();
                    oSheetold.get_Range("W" + (i + 2).ToString(), "W" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(22).ToString();
                    oSheetold.get_Range("X" + (i + 2).ToString(), "X" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(23).ToString();
                    oSheetold.get_Range("Y" + (i + 2).ToString(), "Y" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(24).ToString();
                    oSheetold.get_Range("Z" + (i + 2).ToString(), "Z" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(25).ToString();
                    oSheetold.get_Range("AA" + (i + 2).ToString(), "AA" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(26).ToString();
                    oSheetold.get_Range("AB" + (i + 2).ToString(), "AB" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(27).ToString();
                    oSheetold.get_Range("AC" + (i + 2).ToString(), "AC" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(28).ToString();
                    oSheetold.get_Range("AD" + (i + 2).ToString(), "AD" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(29).ToString();
                    oSheetold.get_Range("AE" + (i + 2).ToString(), "AE" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(30).ToString();
                    oSheetold.get_Range("AF" + (i + 2).ToString(), "AF" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(31).ToString();
                    oSheetold.get_Range("AG" + (i + 2).ToString(), "AG" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(32).ToString();
                    oSheetold.get_Range("AH" + (i + 2).ToString(), "AH" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(33).ToString();
                    oSheetold.get_Range("AI" + (i + 2).ToString(), "AI" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(35).ToString();
                    oSheetold.get_Range("AJ" + (i + 2).ToString(), "AJ" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(36).ToString();
                    oSheetold.get_Range("AK" + (i + 2).ToString(), "AK" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(37).ToString();
                    oSheetold.get_Range("AL" + (i + 2).ToString(), "AL" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(38).ToString();
                    oSheetold.get_Range("AM" + (i + 2).ToString(), "AM" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(39).ToString();
                    oSheetold.get_Range("AN" + (i + 2).ToString(), "AN" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(40).ToString();
                }


           // oXLold.GetSaveAsFilename("Document", "Excel documents (.xls)|*.xls", ".xls", "Saugoti į Excel dokumentą", "Saugoti");
            oWBold.SaveAs(filename);
           oXLold.Quit();
            oSheetold = null;
            oWBold = null;
            oXLold = null;

            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
           /*
            Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
            wordApp.Visible = false;
            object file = filename;

            object XMLtransform = "professional.xsl";

            object nullobj = System.Reflection.Missing.Value;

            object outfilename = Pdf.AppPath + "\\temp.doc";

            object outfileformat = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatDocument;

            object readonlyt = true;

            Microsoft.Office.Interop.Word.Document doc = wordApp.Documents.Open(ref file, ref nullobj, ref readonlyt,

                                                   ref nullobj, ref nullobj, ref nullobj,

                                                   ref nullobj, ref nullobj, ref nullobj,

                                                   ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref XMLtransform);

            wordApp.ActiveWindow.View.ShowXMLMarkup = 0;
            wordApp.TaskPanes[Microsoft.Office.Interop.Word.WdTaskPanes.wdTaskPaneXMLDocument].Visible = false;

            doc.SaveAs(ref outfilename, ref outfileformat, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj);
            doc.Close(ref nullobj, ref nullobj, ref nullobj);
            wordApp.Quit(ref nullobj, ref nullobj, ref nullobj);
            wordApp = null;
            */
        }

    /*    public static void XMLandXSLtoWord()
        {
            DataSet ds;
            XmlDataDocument xmlDoc;
            XslCompiledTransform xslTran;
            XmlElement root;
            XPathNavigator nav;
            XmlTextWriter writer;
            try
            {
                //Create the DataSet from the XML file
                ds = new DataSet();
                ds.ReadXml("sampleMemo.xml");
                //Create the XML from the DataSet
                xmlDoc = new XmlDataDocument(ds);
                //Load the XSLT for Transformation
                xslTran = new XslCompiledTransform();
                xslTran.Load("professional.xsl");
                //Determine the Root object in the XML
                root = xmlDoc.DocumentElement;
                //Create the XPath Navigator to navigate throuth the XML
                nav = root.CreateNavigator();
                //First delete the RTF, if already exist
                if (File.Exists("sample.rtf"))
                {
                    File.Delete("sample.rtf");
                }
                //Create the RTF by Transforming the XML and XSLT
                writer = new XmlTextWriter("sample.rtf", System.Text.Encoding.Default);
                xslTran.Transform(nav, writer);
                //Close the Writer after Transformation
                writer.Close();
                //Release all objects
                writer = null;
                nav = null;
                root = null;
                xmlDoc = null;
                ds = null;
            }
            catch (Exception ex)
            {
                writer = null;
                nav = null;
                root = null;
                xmlDoc = null;
                ds = null;
            }
        }

    
        public static void printWORD(WinWordControl.WinWordControl word, string printer)
        {
            object nullobj = System.Reflection.Missing.Value;

            object background = true;
            object range = Microsoft.Office.Interop.Word.WdPrintOutRange.wdPrintAllDocument;
            object item = Microsoft.Office.Interop.Word.WdPrintOutItem.wdPrintDocumentContent;
            object copies = 1;
            object pages = "";
            object pagetype = Microsoft.Office.Interop.Word.WdPrintOutPages.wdPrintAllPages;
            object printtofile = false;
            object manualduplexprint = false;
            object printzoomcolumn = 0;
            object printzoomrow = 0;
            object printzoompaperwidth = 0;
            object printzoompaperheight = 0;

            word.document.Application.ActivePrinter = printer;
            //word.d
            //word.document.
            word.document.PrintOut(ref background, ref nullobj, ref range, ref nullobj, ref nullobj, ref nullobj, ref item, ref copies, ref pages, ref pagetype, ref printtofile, ref nullobj, ref nullobj, ref manualduplexprint, ref printzoomcolumn, ref printzoomrow, ref printzoompaperwidth, ref printzoompaperheight);


        }

      
        public static void printExcel(ExcelWrapper ew, string filename, string printer)
        {
           
            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel.Workbook wb = ew.GetActiveWorkbook(filename);
            object mis = System.Reflection.Missing.Value;
            object activeprinter = printer;
            object printtofile = false;
            //wb.Save();
            wb.PrintOut(mis, mis, 1, mis, activeprinter, printtofile, mis, mis);
            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;

        }

        public static void saveFileWORD(WinWordControl.WinWordControl word, string newname)
        {
            object nullobj = System.Reflection.Missing.Value;

            object outfilename = newname;

            object outfileformat = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatDocument;

            word.document.SaveAs(ref outfilename, ref outfileformat, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj);
        }

        public static void saveFileExcel(ExcelWrapper ew, string filename, string newname, string template)
        {
            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel.Workbook wb = ew.GetActiveWorkbook(filename);
            object mis = System.Reflection.Missing.Value;
            object filenameE = newname;
            object fileformat = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode access = Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive;
            wb.SaveAs(filenameE, fileformat, mis, mis, mis, mis, access, mis, mis, mis, mis, mis);
            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
        }

        public static void createWord()
        {
            Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
            wordApp.Visible = false;
            object file = Pdf.AppPath + "\\temp.doc";

            object XMLtransform = "professional.xsl";

            object nullobj = System.Reflection.Missing.Value;

            object outfilename = Pdf.AppPath + "\\Ataskaita.doc";

            object outfileformat = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatDocument;

            object readonlyt = true;

            Microsoft.Office.Interop.Word.Document doc = wordApp.Documents.Open(ref file, ref nullobj, ref readonlyt,

                                                   ref nullobj, ref nullobj, ref nullobj,

                                                   ref nullobj, ref nullobj, ref nullobj,

                                                   ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref XMLtransform);

            try
            {
                string file = Pdf.AppPath + "\\lrytas_tv_logo_copy_.jpg";
                string path = System.IO.Path.GetFullPath(file);
                start1 = 0;
                object rng = word.document.Paragraphs.Last.Range;
                doc.Paragraphs.Add(ref rng);
                doc.Paragraphs.Add(ref rng);
                doc.Paragraphs.Add(ref rng);
                doc.Paragraphs.Add(ref rng);
                doc.Paragraphs.Add(ref rng);
                doc.Paragraphs.Add(ref rng);
                doc.Paragraphs.Items(1).Range.InlineShapes.AddPicture(path, ref linktofile, ref savewithfiles, ref rng);
                doc.Paragraphs.Item(1).Range.InlineShapes.Item(1).Height = 71;
                doc.Paragraphs.Item(1).Range.InlineShapes.Item(1).Width = 450;//716;
                doc.Paragraphs.Item(1).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
            }
            catch (Exception ex) { }

            doc.SaveAs(ref outfilename, ref outfileformat, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj, ref nullobj);
            doc.Close(ref nullobj, ref nullobj, ref nullobj);
            wordApp.Quit(ref nullobj, ref nullobj, ref nullobj);
            wordApp = null;
        }*/

        static void exportToWordOld(string filename, DataGrid dt)
        {
        }

        public static void exportToWordNewer(string name, DataGrid dt)
        {
            Microsoft.Office.Interop.Word.Application apWord = new Microsoft.Office.Interop.Word.Application();
            apWord.Visible = false;
            object miss=Type.Missing;
            object vis = true;
            apWord.Documents.Add(ref miss, ref miss, ref miss, ref vis);
            apWord.Documents[1].PageSetup.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperCustom;
            apWord.Documents[1].PageSetup.RightMargin = 0;
            apWord.Documents[1].PageSetup.LeftMargin = 0;
            apWord.Documents[1].PageSetup.TopMargin = 0;
            apWord.Documents[1].PageSetup.BottomMargin = 0;
            apWord.Documents[1].PageSetup.PageWidth=1584;
            apWord.Documents[1].PageSetup.PageHeight=1584;
            //apWord.Documents[1].PageSetup.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA3;
            apWord.Documents[1].PageSetup.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientLandscape;
            //apWord.Documents[1].ActiveWindow.View.Type = Microsoft.Office.Interop.Word.WdViewType.wdOutlineView;
            apWord.Documents[1].ActiveWindow.View.TableGridlines = true;
            //apWord.Documents[0].ActiveWindow.View.Zoom.Percentage = 50;
            object nullobj = System.Reflection.Missing.Value;
            object linktofile = false;
            object savewithfiles = true;
            object start1 = 1;
            object end1 = 0;
          
                try
                {
                   // string file = Pdf.AppPath + "\\Lrytas_tv.png";
                    string file = "";
                    string path = System.IO.Path.GetFullPath(file);
                    start1 = 0;
                    object rng = apWord.Documents[1].Paragraphs.Last.Range;
                    apWord.Documents[1].Paragraphs.Add(ref rng);
                    apWord.Documents[1].Paragraphs.Add(ref rng);
                    apWord.Documents[1].Paragraphs.Add(ref rng);
                    apWord.Documents[1].Paragraphs.Add(ref rng);
                    apWord.Documents[1].Paragraphs.Add(ref rng);
                    apWord.Documents[1].Paragraphs.Add(ref rng);
                    apWord.Documents[1].Paragraphs[1].Range.InlineShapes.AddPicture(path, ref linktofile, ref savewithfiles, ref rng);
                    apWord.Documents[1].Paragraphs[1].Range.InlineShapes[1].Height = 30;
                    apWord.Documents[1].Paragraphs[1].Range.InlineShapes[1].Width = 100;//716;
                    apWord.Documents[1].Paragraphs[1].Range.Paragraphs.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;
                            
         
                start1 = 0;
                object rngg = apWord.Documents[1].Paragraphs.Last.Range;
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs[2].Range.Font.Size = 16;
                apWord.Documents[1].Paragraphs[2].Range.Font.Bold = 1;
                apWord.Documents[1].Paragraphs[2].Range.Paragraphs.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                apWord.Documents[1].Paragraphs[2].Range.InsertBefore("Duomenų exportas");



               // apWord.Documents[1].Paragraphs.Last.Range.InsertParagraphAfter();
               // apWord.Documents[1].Paragraphs.Last.Range.InsertParagraphAfter();

                //nr++;
               // apWord.Documents[1].Paragraphs[apWord.Documents[1].Paragraphs.Count].Range.Font.Size = 12;
               // apWord.Documents[1].Paragraphs[apWord.Documents[1].Paragraphs.Count].Range.Font.Bold = 0;
               // apWord.Documents[1].Paragraphs[apWord.Documents[1].Paragraphs.Count].Range.Paragraphs.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                // apWord.Documents[1].Paragraph[apWord.Documents[1].Paragraphs.Count].Range.InsertAfter(name.Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ')); //int

               
                Object defaultTableBehavior = Type.Missing;
                Object autoFitBehavior = Type.Missing;

                //word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                //word.document.Paragraphs.Last.Range.InsertParagraphAfter();
               // word.document.Paragraphs.Last.Range.Font.Size = 10;
               // word.document.Paragraphs.Last.Range.Font.Bold = 0;
               // word.document.Paragraphs.Last.Range.Paragraphs.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphLeft;

                Microsoft.Office.Interop.Word.Range rngw = apWord.Documents[1].Paragraphs[4].Range;
                    // Microsoft.Office.Interop.Word.Range rngw = apWord.Documents[1].Paragraphs.Last.Range;

                int rowcount = dt.Items.Count;
                apWord.Documents[1].Tables.Add(rngw, rowcount, 40, ref defaultTableBehavior, ref autoFitBehavior);
                apWord.Documents[1].Tables[1].Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                apWord.Documents[1].Tables[1].Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
                //apWord.Documents[1].Tables[1].Columns.Width = 200;
                apWord.Documents[1].Tables[1].Cell(1, 1).Range.InsertAfter("Kasetes Nr.");
                apWord.Documents[1].Tables[1].Cell(1, 2).Range.InsertAfter("Kompanija");
                apWord.Documents[1].Tables[1].Cell(1, 3).Range.InsertAfter("Pavadinimas originalo kalba");
                apWord.Documents[1].Tables[1].Cell(1, 4).Range.InsertAfter("Lietuviškas pavadinimas");
                apWord.Documents[1].Tables[1].Cell(1, 5).Range.InsertAfter("Trukmė 1");
                apWord.Documents[1].Tables[1].Cell(1, 6).Range.InsertAfter("Trukmė 2");
                apWord.Documents[1].Tables[1].Cell(1, 7).Range.InsertAfter("TC trukmė");
                apWord.Documents[1].Tables[1].Cell(1, 8).Range.InsertAfter("Pradzios TC");
                apWord.Documents[1].Tables[1].Cell(1, 9).Range.InsertAfter("I Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 10).Range.InsertAfter("II Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 11).Range.InsertAfter("III Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 12).Range.InsertAfter("IV Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 13).Range.InsertAfter("V Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 14).Range.InsertAfter("VI Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 15).Range.InsertAfter("VII Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 16).Range.InsertAfter("VIII Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 17).Range.InsertAfter("IX Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 18).Range.InsertAfter("X Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 19).Range.InsertAfter("XI Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 20).Range.InsertAfter("XII Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 21).Range.InsertAfter("XIII Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 22).Range.InsertAfter("XIV Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 23).Range.InsertAfter("XV Įsikirtimas");
                apWord.Documents[1].Tables[1].Cell(1, 24).Range.InsertAfter("Eteris 1");
                apWord.Documents[1].Tables[1].Cell(1, 25).Range.InsertAfter("Eteris 2");
                apWord.Documents[1].Tables[1].Cell(1, 26).Range.InsertAfter("Eteris 3");
                apWord.Documents[1].Tables[1].Cell(1, 27).Range.InsertAfter("Eteris 4");
                apWord.Documents[1].Tables[1].Cell(1, 28).Range.InsertAfter("Eteris 5");
                apWord.Documents[1].Tables[1].Cell(1, 29).Range.InsertAfter("P raidė");
                apWord.Documents[1].Tables[1].Cell(1, 30).Range.InsertAfter("Intarpai bėg#eil");
                apWord.Documents[1].Tables[1].Cell(1, 31).Range.InsertAfter("Licencijos pradžia");
                apWord.Documents[1].Tables[1].Cell(1, 32).Range.InsertAfter("Licencijos pabaiga");
                apWord.Documents[1].Tables[1].Cell(1, 33).Range.InsertAfter("Žanras");
                apWord.Documents[1].Tables[1].Cell(1, 34).Range.InsertAfter("Režisierius");
                apWord.Documents[1].Tables[1].Cell(1, 35).Range.InsertAfter("Aktoriai");
                apWord.Documents[1].Tables[1].Cell(1, 36).Range.InsertAfter("Šalis");
                apWord.Documents[1].Tables[1].Cell(1, 37).Range.InsertAfter("Metai");
                apWord.Documents[1].Tables[1].Cell(1, 38).Range.InsertAfter("Sezonas");
                apWord.Documents[1].Tables[1].Cell(1, 39).Range.InsertAfter("Serija");
                apWord.Documents[1].Tables[1].Cell(1, 40).Range.InsertAfter("Indeksas");
                for (int i = 0; i < dt.Items.Count - 1; i++)
                {
                    DataRowView li = (DataRowView)dt.Items[i];
                    apWord.Documents[1].Tables[1].Cell(i+2, 1).Range.InsertAfter(li.Row.ItemArray.GetValue(0).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 2).Range.InsertAfter(li.Row.ItemArray.GetValue(1).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 3).Range.InsertAfter(li.Row.ItemArray.GetValue(2).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 4).Range.InsertAfter(li.Row.ItemArray.GetValue(3).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 5).Range.InsertAfter(li.Row.ItemArray.GetValue(4).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 6).Range.InsertAfter(li.Row.ItemArray.GetValue(5).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 7).Range.InsertAfter(li.Row.ItemArray.GetValue(6).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 8).Range.InsertAfter(li.Row.ItemArray.GetValue(7).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 9).Range.InsertAfter(li.Row.ItemArray.GetValue(8).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 10).Range.InsertAfter(li.Row.ItemArray.GetValue(9).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 11).Range.InsertAfter(li.Row.ItemArray.GetValue(10).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 12).Range.InsertAfter(li.Row.ItemArray.GetValue(11).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 13).Range.InsertAfter(li.Row.ItemArray.GetValue(12).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 14).Range.InsertAfter(li.Row.ItemArray.GetValue(13).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 15).Range.InsertAfter(li.Row.ItemArray.GetValue(14).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 16).Range.InsertAfter(li.Row.ItemArray.GetValue(15).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 17).Range.InsertAfter(li.Row.ItemArray.GetValue(16).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 18).Range.InsertAfter(li.Row.ItemArray.GetValue(17).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 19).Range.InsertAfter(li.Row.ItemArray.GetValue(18).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 20).Range.InsertAfter(li.Row.ItemArray.GetValue(19).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 21).Range.InsertAfter(li.Row.ItemArray.GetValue(20).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 22).Range.InsertAfter(li.Row.ItemArray.GetValue(21).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 23).Range.InsertAfter(li.Row.ItemArray.GetValue(22).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 24).Range.InsertAfter(li.Row.ItemArray.GetValue(23).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 25).Range.InsertAfter(li.Row.ItemArray.GetValue(24).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 26).Range.InsertAfter(li.Row.ItemArray.GetValue(25).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 27).Range.InsertAfter(li.Row.ItemArray.GetValue(26).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 28).Range.InsertAfter(li.Row.ItemArray.GetValue(27).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 29).Range.InsertAfter(li.Row.ItemArray.GetValue(28).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 30).Range.InsertAfter(li.Row.ItemArray.GetValue(29).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 31).Range.InsertAfter(li.Row.ItemArray.GetValue(30).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 32).Range.InsertAfter(li.Row.ItemArray.GetValue(31).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 33).Range.InsertAfter(li.Row.ItemArray.GetValue(32).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 34).Range.InsertAfter(li.Row.ItemArray.GetValue(33).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 35).Range.InsertAfter(li.Row.ItemArray.GetValue(35).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2,36).Range.InsertAfter(li.Row.ItemArray.GetValue(36).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 37).Range.InsertAfter(li.Row.ItemArray.GetValue(37).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 38).Range.InsertAfter(li.Row.ItemArray.GetValue(38).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 39).Range.InsertAfter(li.Row.ItemArray.GetValue(39).ToString());
                    apWord.Documents[1].Tables[1].Cell(i+2, 40).Range.InsertAfter(li.Row.ItemArray.GetValue(40).ToString());
                }

                // word.document.Tables.Item(0).Cell(3, 2).Range.InsertAfter(col2row3);
              /*  if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                {
                    word.document.Tables.Item(tbcnt).Cell(3, 3).Range.InsertAfter("");
                }
                else
                {
                    word.document.Tables.Item(tbcnt).Cell(3, 3).Range.InsertAfter(col2row3);
                }

                word.document.Tables.Item(tbcnt).Columns.Item(1).SetWidth(100, Word.WdRulerStyle.wdAdjustSameWidth);
                word.document.Tables.Item(tbcnt).Columns.Item(2).SetWidth(180, Word.WdRulerStyle.wdAdjustSameWidth);
                word.document.Tables.Item(tbcnt).Columns.Item(3).SetWidth(180, Word.WdRulerStyle.wdAdjustSameWidth);
                //word.document.Paragraphs.Last.Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                // word.document.Paragraphs.Last.Range.InsertParagraphAfter();

                //string sqlres = "select ROW_NUMBER()OVER (order by DelegateText.name asc) as Nr,name,case when Vote IS NULL then '' else Vote end as vt from DelegateList left join DelegateText on DelegateList.delegate_id=DelegateText.delegate_id left join (select DelegateList.delegate_id as subid,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote3=1 then 'Susilaikė' else case when VotingResult.Vote2=1 then 'Prieš' end end end as Vote from DelegateList left join DelegateText on DelegateList.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where DelegateList.conference_id=" + guid + " and VotingSession.id=" + id + ") as tbs on DelegateList.delegate_id=tbs.subid where conference_id=" + guid + " order by name asc";
                if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                {
                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Size = 10;
                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Bold = 0;
                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.InsertAfter("REGISTRAVOSI");
                }

                defaultTableBehavior = Type.Missing;
                autoFitBehavior = Type.Missing;
                rngw = word.document.Paragraphs.Last.Range;
                word.document.Paragraphs.Last.Range.Font.Size = 10;
                word.document.Paragraphs.Last.Range.Font.Bold = 0;
                word.document.Paragraphs.Last.Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;




                string sqlres1 = "select distinct LogDelegate.delegate_id as subid,DelegateText.name,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'UŽ' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'SUSILAIKĖ' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'PRIEŠ' end end end as vt from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where LogDelegate.conference_id=" + guid + " and VotingSession.id=" + id + " ORDER BY DelegateText.name ASC";
                DataTable tbres1 = db.getTable(sqlres1);
                if (tbres1.Rows.Count > 0)
                {
                    tbres1 = Functionality.sortTableByName(tbres1);
                    int rscnt = tbres1.Rows.Count;
                    if (rscnt == 0)
                    {
                        rscnt = 1;
                    }
                    word.document.Tables.Add(rngw, rscnt, 3, ref defaultTableBehavior, ref autoFitBehavior);
                    tbcnt++;
                    Int64 yy = 0;
                    for (int y = 0; y < tbres.Rows.Count; y++)
                    {
                        if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                        {
                            if (tbres1.Rows[y]["vt"].ToString().Contains("UŽ"))
                            {
                                row++;
                                yy++;
                                word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
                                word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.InsertAfter((yy).ToString());//tbres.Rows[y]["Nr"].ToString());
                                word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                                word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.InsertAfter(tbres1.Rows[y]["name"].ToString());
                                word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                                word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.InsertAfter("");
                            }
                        }
                        else
                        {
                            row++;
                            word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
                            word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.InsertAfter((y + 1).ToString());//tbres.Rows[y]["Nr"].ToString());
                            word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                            word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.InsertAfter(tbres1.Rows[y]["name"].ToString());
                            word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                            word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.InsertAfter(tbres1.Rows[y]["vt"].ToString().Replace("SUSILAIKE", "SUSILAIKĖ"));
                        }

                    }
                }
                word.document.Tables.Item(tbcnt).Columns.Item(1).SetWidth(170, Word.WdRulerStyle.wdAdjustSameWidth);
                word.document.Tables.Item(tbcnt).Columns.Item(2).SetWidth(150, Word.WdRulerStyle.wdAdjustSameWidth);
                word.document.Tables.Item(tbcnt).Columns.Item(3).SetWidth(80, Word.WdRulerStyle.wdAdjustSameWidth);

                word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                object dir = Microsoft.Office.Interop.Word.WdCollapseDirection.wdCollapseEnd;
                word.document.Paragraphs.Last.Range.Collapse(ref dir);
                object tp = Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
                word.document.Paragraphs.Last.Range.InsertBreak(ref tp);*/

                }
                catch (Exception ex) { }
                //word.document.Paragraphs.Item(0).Range.InsertAfter(Configuration.txt_text);

            

        /*    if ((!meeting_title.Equals("")) && (!guid.Equals("")))
            {
                DataBase db = new DataBase(Configuration.dbServer, Configuration.db_user, Configuration.db_password, Configuration.db);

                //sena DataTable tb = db.getTable("select date,time from StoryStartTime_tbl where guid='" + guid + "'");
                string sqllt = "SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR,starttime,102),0,11),'.','-') as datet,SUBSTRING(CONVERT(VARCHAR,starttime,8),0,9) as timet,REPLACE(SUBSTRING(CONVERT(VARCHAR,stoptime,102),0,11),'.','-') as dates,SUBSTRING(CONVERT(VARCHAR,stoptime,8),0,9) as times, REPLACE(SUBSTRING(CONVERT(VARCHAR,(stoptime-starttime),102),0,11),'.','-') as datel,SUBSTRING(CONVERT(VARCHAR,(stoptime-starttime),8),0,9) as timel from logConference where conference_id=" + guid + " order by timet asc";
                DataTable tb = db.getTable(sqllt);

                string startDate = "";
                string startTime = "";
                string stopDate = "";
                string stopTime = "";
                string lastingDate = "";
                string lastingTime = "";
                if (tb.Rows.Count > 0)
                {
                    startDate = tb.Rows[0]["datet"].ToString();
                    startTime = tb.Rows[0]["timet"].ToString();
                    stopDate = tb.Rows[tb.Rows.Count - 1]["dates"].ToString();
                    stopTime = tb.Rows[tb.Rows.Count - 1]["times"].ToString();
                    lastingDate = tb.Rows[0]["datel"].ToString();
                    lastingTime = Functionality.getDurationConference(tb);//tb.Rows[0]["timel"].ToString();
                }

                if (template.Equals("Vardinių balsavimų ataskaita"))
                {
                    start1 = 0;
                    object rng = word.document.Paragraphs.Last.Range;

                    string sql = "SELECT  Agenda.SortOrder as so,Agenda.ItemNumber as itemnr,max(VotingSession.Version) as vr,VotingSession.Id as id, AgendaText.Agenda_id as aid, case when REPLACE(REPLACE(REPLACE(REPLACE(Description,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'') = '' then Name else case when Description IS NULL then Name else Description end end as FullName, case when Quorum = '0' then 'NĖRA KVORUMO' else case when Passed = '1' then 'PRIIMTAS' else 'NEPRIIMTAS' end end as VoteRes, 'UŽ - '+CAST(" + Configuration.rd_uz + " as varchar) as res1, 'SUSILAIKĖ - '+CAST(" + Configuration.rd_susilaike + " as varchar) as res3, 'PRIEŠ - '+CAST(" + Configuration.rd_pries + " as varchar) as res2, 'NEBALSAVO - '+CAST(VotingSession.Result5 as varchar) as res5, 'BALSAVIME DALYVAVO - '+CAST(VotingSession.Result6 as varchar) as res6 from AgendaText left join VotingSession on VotingSession.Agenda_id=AgendaText.Agenda_id left join Agenda on Agenda.Id=AgendaText.Agenda_id where AgendaText.Agenda_id in (Select ID from Agenda where Conference_id=" + guid + " ) group by Agenda.SortOrder,Agenda.ItemNumber,AgendaText.Agenda_id, AgendaText.Name, AgendaText.Description,VotingSession.Passed,VotingSession.Quorum,VotingSession.Result1,VotingSession.Result2,VotingSession.Result3,VotingSession.Result5,VotingSession.Result6,VotingSession.Id order by so asc";

                    DataTable tbagenda = db.getTable(sql);

                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    for (int i = 0; i < tbagenda.Rows.Count * 5; i++)
                    {
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                    }
                    word.document.Paragraphs.Item(2).Range.Font.Size = 20;
                    word.document.Paragraphs.Item(2).Range.Font.Bold = 0;
                    word.document.Paragraphs.Item(2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                    word.document.Paragraphs.Item(2).Range.InsertBefore("Vardinių balsavimų ataskaita");


                    word.document.Paragraphs.Item(3).Range.Font.Size = 12;
                    word.document.Paragraphs.Item(3).Range.Font.Bold = 0;
                    word.document.Paragraphs.Item(3).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;

                    word.document.Paragraphs.Item(3).Range.InsertAfter("Posėdžio pavadinimas:" + meeting_title);
                    word.document.Paragraphs.Item(4).Range.InsertAfter("Posėdžio data: " + startDate + " " + startTime);

                    int nr = 0;
                    if (tbagenda.Rows.Count > 0)
                    {
                        // word.document.Paragraphs.Item(6).Range.InsertAfter("Svarstytų klausimų sąrašas:");
                        int row = 7;
                        for (int i = 0; i < tbagenda.Rows.Count; i++)
                        {
                      
                            string paragraph = "";
                            string itemnumber = "";
                            try
                            {
                                itemnumber = tbagenda.Rows[i]["itemnr"].ToString();
                            }
                            catch (Exception ex) { }
                            string version = tbagenda.Rows[i]["vr"].ToString();
                            //sena2 string name = tbagenda.Rows[i]["Name"].ToString();
                            string name = tbagenda.Rows[i]["FullName"].ToString();
                            string status = tbagenda.Rows[i]["VoteRes"].ToString();
                            string res1 = tbagenda.Rows[i]["res1"].ToString();
                            string res2 = tbagenda.Rows[i]["res2"].ToString();
                            string res3 = tbagenda.Rows[i]["res3"].ToString();
                            string res5 = tbagenda.Rows[i]["res5"].ToString();
                            string res6 = tbagenda.Rows[i]["res6"].ToString();
                            string id = tbagenda.Rows[i]["id"].ToString();
                            string aid = tbagenda.Rows[i]["aid"].ToString();
                            string aid_next = "0";
                            try
                            {
                                aid_next = tbagenda.Rows[i + 1]["aid"].ToString();
                            }
                            catch (Exception ex) { aid_next = "0"; }
                            if (!aid.Equals(aid_next))
                            {
                                //sena2 word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + " " + name + "  [Statusas:" + status + "]");
                                nr++;
                                 word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + itemnumber + " " + name.Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' '));
                                row++;
                                row++;
                                word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + res6);
                                row++;
                                word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + res1);
                                row++;
                                word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + res2);
                                row++;
                                word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + res3.Replace("SUSILAIKE", "SUSILAIKĖ"));
                                row++;
                                word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + res5);
                                row++;
                                row++;

                                List<Functionality.DelegateObj> lsdt = new List<Functionality.DelegateObj>();
                                foreach (Functionality.DelegateObj dob in Functionality.lsd)
                                {
                                    lsdt.Add(dob);
                                }

                                if (res1.Equals("") && res2.Equals("") && res3.Equals("") && res5.Equals(""))
                                {
                                    word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + "ŠIUO KLAUSIMU NEBUVO BALSUOJAMA");
                                }
                                else
                                {
                                    word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + "KLAUSIMAS " + status.Replace("NERA", "NEPRIIMTAS - NĖRA"));
                                }
                                row++;
                                row++;
                                 string fieldname = "VotingResult.Vote1";
                                switch (Configuration.rd_uz)
                                {
                                    case "VotingSession.Result1": fieldname = "VotingResult.Vote1"; break;
                                    case "VotingSession.Result2": if (Configuration.rd_pries.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote3"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote3"; } break;
                                    case "VotingSession.Result3": if (Configuration.rd_pries.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } break;
                                }
                                string sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Už' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Susilaikė' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and " + fieldname + "=1 order by Vote,DelegateText.name";//Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote")
                                DataTable tbvotings = db.getTable(sql1);

                                if (tbvotings.Rows.Count > 0)
                                {
                                    word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + "BALSAVO UŽ:");
                                    row++;
                                    for (int y = 0; y < tbvotings.Rows.Count; y++)
                                    {
                                        string votingname = tbvotings.Rows[y]["named"].ToString();
                                        Functionality.findInLsdAndRemove(lsdt, votingname);
                                        string vote = tbvotings.Rows[y]["Vote"].ToString();
                                        word.document.Paragraphs.Item(row).Range.InsertAfter("            " + votingname);
                                        row++;
                                    }
                                    row++;
                                }

                                // sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote2=1 then 'Susilaikė' else case when VotingResult.Vote3=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and VotingResult.Vote2=1 order by Vote,DelegateText.name";
                                //sena4 sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote3=1 then 'Susilaikė' else case when VotingResult.Vote2=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and VotingResult.Vote3=1 order by Vote,DelegateText.name";
                                fieldname = "VotingResult.Vote2";
                                switch (Configuration.rd_pries)
                                {
                                    case "VotingSession.Result1": if (Configuration.rd_uz.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote1"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote1"; } break;
                                    case "VotingSession.Result2": if (Configuration.rd_uz.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } break;
                                    case "VotingSession.Result3": fieldname = "VotingResult.Vote3"; break;
                                }
                                sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Už' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Susilaikė' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and " + fieldname + "=1 order by Vote,DelegateText.name"; //Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote")
                                tbvotings = db.getTable(sql1);

                                if (tbvotings.Rows.Count > 0)
                                {
                                    word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + "BALSAVO PRIEŠ:");
                                    row++;
                                    for (int y = 0; y < tbvotings.Rows.Count; y++)
                                    {
                                        string votingname = tbvotings.Rows[y]["named"].ToString();
                                        Functionality.findInLsdAndRemove(lsdt, votingname);
                                        string vote = tbvotings.Rows[y]["Vote"].ToString();
                                        word.document.Paragraphs.Item(row).Range.InsertAfter("            " + votingname);
                                        row++;
                                    }
                                    row++;
                                }

                                //sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote2=1 then 'Susilaikė' else case when VotingResult.Vote3=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and VotingResult.Vote3=1 order by Vote,DelegateText.name";
                                //sena4 sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote2=1 then 'Susilaikė' else case when VotingResult.Vote3=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and VotingResult.Vote2=1 order by Vote,DelegateText.name";
                                fieldname = "VotingResult.Vote3";
                                switch (Configuration.rd_susilaike)
                                {
                                    case "VotingSession.Result1": if (Configuration.rd_pries.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote1"; } else if (Configuration.rd_uz.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote1"; } break;
                                    case "VotingSession.Result2": fieldname = "VotingResult.Vote2"; break;
                                    case "VotingSession.Result3": if (Configuration.rd_pries.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote2"; } else if (Configuration.rd_uz.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote1"; } break;
                                }
                                sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Už' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Susilaikė' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and " + fieldname + "=1 order by Vote,DelegateText.name"; //Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote")
                                tbvotings = db.getTable(sql1);

                                if (tbvotings.Rows.Count > 0)
                                {
                                    word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + "SUSILAIKĖ:");
                                    row++;
                                    for (int y = 0; y < tbvotings.Rows.Count; y++)
                                    {
                                        string votingname = tbvotings.Rows[y]["named"].ToString();
                                        Functionality.findInLsdAndRemove(lsdt, votingname);
                                        string vote = tbvotings.Rows[y]["Vote"].ToString();
                                        word.document.Paragraphs.Item(row).Range.InsertAfter("            " + votingname);
                                        row++;
                                    }
                                    row++;
                                }

                                if (lsdt.Count > 0)
                                {
                                    word.document.Paragraphs.Item(row).Range.InsertAfter(paragraph + "NEBALSAVO:");
                                    row++;
                                    foreach (Functionality.DelegateObj dob in lsdt)
                                    {
                                        word.document.Paragraphs.Item(row).Range.InsertAfter("            " + dob.name);
                                        row++;
                                    }
                                    row++;
                                }


                            }
                        }
                    }

                }
                if (template.Equals("Posėdžio dalyvių ataskaita"))
                {
                    start1 = 0;
                    object rng = word.document.Paragraphs.Last.Range;
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    for (int i = 0; i < 10; i++)
                    {
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                    }


                    word.document.Paragraphs.Item(2).Range.Font.Size = 20;
                    word.document.Paragraphs.Item(2).Range.Font.Bold = 0;
                    word.document.Paragraphs.Item(2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                    word.document.Paragraphs.Item(2).Range.InsertBefore("Posėdžio dalyvių ataskaita");


                    word.document.Paragraphs.Item(3).Range.Font.Size = 12;
                    word.document.Paragraphs.Item(3).Range.Font.Bold = 0;
                    word.document.Paragraphs.Item(3).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;

                    word.document.Paragraphs.Item(3).Range.InsertAfter("Posėdžio pavadinimas:" + meeting_title);
                    word.document.Paragraphs.Item(4).Range.InsertAfter("Posėdžio pradžia: " + startDate + " " + startTime);
                    word.document.Paragraphs.Item(5).Range.InsertAfter("Posėdžio pabaiga: " + stopDate + " " + stopTime);


                     if (Functionality.lsd.Count > 0)
                    {
                        int i = 0;
                        foreach (Functionality.DelegateObj dob in Functionality.lsd)
                        {
                            word.document.Paragraphs.Item(i + 8).Range.InsertAfter(dob.nr + ") " + dob.name + "      " + dob.type);
                            i++;
                        }
                    }
                 
                }
                if (template.Equals("Tarybos narių aktyvumo ataskaita"))
                {
                    start1 = 0;
                    object rng = word.document.Paragraphs.Last.Range;
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);
                    word.document.Paragraphs.Add(ref rng);


                    word.document.Paragraphs.Item(2).Range.Font.Size = 20;
                    word.document.Paragraphs.Item(2).Range.Font.Bold = 0;
                    word.document.Paragraphs.Item(2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                    word.document.Paragraphs.Item(2).Range.InsertBefore("Tarybos narių aktyvumo ataskaita");


                    word.document.Paragraphs.Item(3).Range.Font.Size = 12;
                    word.document.Paragraphs.Item(3).Range.Font.Bold = 0;
                    word.document.Paragraphs.Item(3).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;

                    word.document.Paragraphs.Item(3).Range.InsertAfter("Posėdžio pavadinimas:" + meeting_title);
                    word.document.Paragraphs.Item(4).Range.InsertAfter("Posėdžio data: " + startDate + " " + startTime);
                    word.document.Paragraphs.Item(5).Range.InsertAfter("Posėdžio pabaiga: " + stopDate + " " + stopTime);
                    word.document.Paragraphs.Item(6).Range.InsertAfter("Posėdžio trukmė: " + lastingTime);

                    //sena DataTable tbagenda = db.getTable("SELECT * FROM DelegateInfoFinal_tbl where guids='" + guid + "' order by delegate_id asc");
                    string sql = "SELECT distinct DelegateText.name as name,LogDelegate.delegate_id as del_id from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id where conference_id=" + guid + " and (UserTypeText.name='Tarybos narys' or UserTypeText.name='Pirmininkaujantis')";
                    //sena2 DataTable tbagenda = db.getTable(sql);
                    DataTable tbagenda = db.getTable("SELECT distinct DelegateText.name as name, UserTypeText.name as type, rank() OVER (ORDER BY DelegateText.name) as nr,LogDelegate.delegate_id as del_id from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id left join Delegate on Delegate.id=LogDelegate.delegate_id left join UserTypeText on UserTypeText.usertype_id=Delegate.usertype_id where conference_id=" + guid + " and (UserTypeText.name='Tarybos narys' or UserTypeText.name='Pirmininkaujantis') group by DelegateText.name,UserTypeText.name,LogDelegate.delegate_id ");
                    rng = word.document.Paragraphs.Last.Range;
                    for (int i = 0; i < tbagenda.Rows.Count * 5; i++)
                    {
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                        word.document.Paragraphs.Add(ref rng);
                    }

                    int row = 9;
                    if (tbagenda.Rows.Count > 0)
                    {
                        // word.document.Paragraphs.Item(6).Range.InsertAfter("Dalyvavusių posėdyje tarybos narių sąrašas:");

                        for (int i = 0; i < tbagenda.Rows.Count; i++)
                        {
                                string delegate_id = tbagenda.Rows[i]["del_id"].ToString();
                            sql = "SELECT DelegateText.name,LogSpeak.startTime,LogSpeak.stopTime,CAST((LogSpeak.stopTime - LogSpeak.startTime) as time(7)) AS Duration from logSpeak left join DelegateText on LogSpeak.delegate_id=DelegateText.delegate_id where conference_id=" + guid + " and LogSpeak.delegate_id=" + delegate_id + " order by LogSpeak.startTime asc;";
                            DataTable tbdelegate = db.getTable(sql);
                            TimeSpan durat = TimeSpan.Parse("00:00:00");
                            if (tbdelegate.Rows.Count > 0)
                            {
                                for (int y = 0; y < tbdelegate.Rows.Count; y++)
                                {
                                    try
                                    {
                                        string dur = tbdelegate.Rows[y]["Duration"].ToString();
                                        TimeSpan currdur = TimeSpan.Parse(dur);
                                        durat = durat + currdur;
                                    }
                                    catch (Exception ex) { }
                                }
                            }

                            int qcount = 0;
                            int qqcount = 0;
                            sql = "SELECT AgendaText.Name,logSubject.starttime,logSubject.stoptime from logSubject left join AgendaText on logSubject.agenda_id=AgendaText.Agenda_id where conference_id=" + guid + " order by logSubject.starttime asc;";
                            DataTable tbquestions = db.getTable(sql);
                            if (tbquestions.Rows.Count > 0)
                            {

                                for (int pp = 0; pp < tbquestions.Rows.Count; pp++)
                                {
                                    try
                                    {
                                        TimeSpan questionstart = TimeSpan.Parse(tbquestions.Rows[pp]["starttime"].ToString().Substring(11, 8));
                                        TimeSpan questionend = TimeSpan.Parse(tbquestions.Rows[pp]["stoptime"].ToString().Substring(11, 8));
                                        sql = "SELECT DelegateText.name,LogSpeak.startTime,LogSpeak.stopTime,CAST((LogSpeak.stopTime - LogSpeak.startTime) as time(7)) AS Duration from logSpeak left join DelegateText on LogSpeak.delegate_id=DelegateText.delegate_id where conference_id=" + guid + " and LogSpeak.delegate_id=" + delegate_id + " order by LogSpeak.startTime asc;";
                                        DataTable tbdelegatet = db.getTable(sql);
                                        if (tbdelegatet.Rows.Count > 0)
                                        {
                                            qcount = 0;
                                            for (int y = 0; y < tbdelegatet.Rows.Count; y++)
                                            {
                                                TimeSpan delegatestart = TimeSpan.Parse(tbdelegatet.Rows[y]["startTime"].ToString().Substring(11, 8));
                                                TimeSpan delegateend = TimeSpan.Parse(tbdelegatet.Rows[y]["stopTime"].ToString().Substring(11, 8));

                                                if ((questionstart <= delegatestart) && (delegateend <= questionend))
                                                {
                                                    qcount++;
                                                }
                                            }
                                            if (qcount > 0) { qqcount++; }
                                        }
                                    }
                                    catch (Exception ex) { }
                                }
                            }
                            string name = tbagenda.Rows[i]["name"].ToString();
                            string nr = tbagenda.Rows[i]["nr"].ToString();
                            string countquestion = qqcount.ToString();
                            string countspeak = tbdelegate.Rows.Count.ToString();
                            string duration = durat.ToString();// durat.Hours.ToString() + ":" + durat.Minutes.ToString()+":"+durat.Seconds.ToString();
                            //string delegatestring = string.Format("{0} aktyvumo duomenys – dalyvavo [{1}] klausimų svarstyme, pasisakė [{2}] kartų, bendra pasisakymų trukmė [{3}] ", name, countquestion, countspeak, duration);
                            //sena string speaker_stat = String.Format("    Dalyvio aktyvumo duomenys – dalyvavo {0} klausimų svarstyme, pasisakė {1} kartų", x_question, y_times);
                            //sena word.document.Paragraphs.Item(row).Range.InsertAfter(speaker_stat);
                            string delegatestring = string.Format("{0}) {1} ", nr, name);//– dalyvavo [{1}] klausimų svarstyme, pasisakė [{2}] kartų, bendra pasisakymų trukmė [{3}] ",nr,name,countquestion,countspeak,duration);
                            word.document.Paragraphs.Item(row).Range.InsertAfter(delegatestring);
                            row++;
                            DataTable vote_t = db.getTable("select count(*) from VotingResult left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where VotingSession.Conference_id=" + guid + " and VotingResult.LogDelegate_id=" + delegate_id);
                            if (vote_t.Rows.Count > 0)
                            {
                                string voten = vote_t.Rows[0][0].ToString();
                                string votingNr = string.Format("Balsavo [{0}] klausimų svarstyme", voten);
                                word.document.Paragraphs.Item(row).Range.InsertAfter("    " + votingNr);
                                row++;
                            }
                            string dlgestring = string.Format("Pasisakė [{0}] kartų, bendra pasisakymų trukmė [{1}] ", countspeak, duration);
                            word.document.Paragraphs.Item(row).Range.InsertAfter("    " + dlgestring);
                            row++;
                            //sena3 DataTable durt_t = db.getTable("select CAST(CAST(SUM(CAST(Duration as float)) as datetime) as time(7)) as summ from (SELECT DelegateText.name as name,LogDelegate.loginTime as logint,LogDelegate.logoutTime as logoutt,CAST((LogDelegate.logoutTime - LogDelegate.loginTime) as datetime) AS Duration from logDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id where conference_id=" + guid + " and LogDelegate.delegate_id=" + delegate_id + ") A ;");
                            // sql = "select CAST(CAST(SUM(CAST(Duration as float)) as datetime) as time(7)) as summ from (SELECT DelegateText.name as name,LogDelegate.loginTime as logint,(case when ((LogDelegate.logoutTime IS NULL) or (CAST(LogDelegate.loginTime as date)<> CAST(LogDelegate.logoutTime as date))) then (SELECT max(stoptime) from logConference where conference_id=" + guid + ") else LogDelegate.logoutTime end) as logoutt,CAST(((case when ((LogDelegate.logoutTime IS NULL) or (CAST(LogDelegate.loginTime as date)<> CAST(LogDelegate.logoutTime as date))) then (SELECT max(stoptime) from logConference where conference_id=" + guid + ") else LogDelegate.logoutTime end) - LogDelegate.loginTime) as datetime) AS Duration from logDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id where conference_id=" + guid + " and LogDelegate.delegate_id=" + delegate_id + ") A ;";
                            sql = "select * from LogDelegate where conference_id=" + guid + " and delegate_id=" + delegate_id + " order by loginTime asc";
                            DataTable durt_t = db.getTable(sql);

                            // sql = "SELECT max(stoptime) from logConference where conference_id=" + guid;
                            // DataTable cnfend = db.getTable(sql);
                            // string durr = Functionality.getDuration(durt_t, cnfend.Rows[0][0].ToString());
                            sql = "SELECT max(stoptime),max(starttime) from logConference where conference_id=" + guid;
                            DataTable cnfend = db.getTable(sql);
                            string durr = Functionality.getDuration(durt_t, cnfend.Rows[0][0].ToString(), cnfend.Rows[0][1].ToString());


                            if (durt_t.Rows.Count > 0)
                            {
                                string durt = "00:00:00";
                                try
                                {
                                    durt = durr;
                                }
                                catch (Exception ex) { }
                                string activityduration = string.Format("Dalyvavimo posėdyje trukmė [{0}]", durt);
                                word.document.Paragraphs.Item(row).Range.InsertAfter("    " + activityduration);
                                row++;
                            }
                            row++;
                        }
                    }

                    //
                    //word.document.Paragraphs.Item(row).Range

                    try
                    {
                        while (true)
                        {
                            word.document.Paragraphs.Item(row).Range.Select();
                            Word.Selection currentSelection = word.document.Application.Selection;
                            object unit = Word.WdUnits.wdCharacter;
                            object count = 1;
                            currentSelection.Delete(ref unit, ref count);
                        }
                    }
                    catch (Exception ex) { }

                }
                if (template.Equals("Atviro balsavimo rezultatų protokolas"))
                {

                    start1 = 0;
                    word.document.Paragraphs.Item(1).Range.Cut();
                    object unit = Type.Missing;
                    object count = 4;
                    word.document.Paragraphs.Item(1).Range.Delete(ref unit, ref count);
                    //  object rng = word.document.Paragraphs.Last.Range;

                    //string sql = "SELECT  Agenda.SortOrder as so,Agenda.ItemNumber as itemnr,max(VotingSession.Version) as vr,VotingSession.Id as id, AgendaText.Agenda_id as aid, case when REPLACE(REPLACE(REPLACE(REPLACE(Description,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'') = '' then Name else case when Description IS NULL then Name else Description end end as FullName, case when Quorum = '0' then 'NĖRA KVORUMO' else case when Passed = '1' then 'PRIIMTAS' else 'NEPRIIMTAS' end end as VoteRes, 'UŽ   '+CAST(" + Configuration.rd_uz + " as varchar) as res1, 'SUSILAIKĖ   '+CAST(" + Configuration.rd_susilaike + " as varchar) as res3, 'PRIEŠ   '+CAST(" + Configuration.rd_pries + " as varchar) as res2, 'NEBALSAVO   '+CAST(VotingSession.Result5 as varchar) as res5, 'Užsiregistravo '+CAST(VotingSession.Result6 as varchar) as res6 from AgendaText left join VotingSession on VotingSession.Agenda_id=AgendaText.Agenda_id left join Agenda on Agenda.Id=AgendaText.Agenda_id where AgendaText.Agenda_id in (Select ID from Agenda where Conference_id=" + guid + " ) group by Agenda.SortOrder,Agenda.ItemNumber,AgendaText.Agenda_id, AgendaText.Name, AgendaText.Description,VotingSession.Passed,VotingSession.Quorum,VotingSession.Result1,VotingSession.Result2,VotingSession.Result3,VotingSession.Result5,VotingSession.Result6,VotingSession.Id order by VotingSession.Id asc";
                    string sql = "SELECT  Agenda.SortOrder as so,Agenda.ItemNumber as itemnr,max(VotingSession.Version) as vr,VotingSession.Id as id, AgendaText.Agenda_id as aid, case when REPLACE(REPLACE(REPLACE(REPLACE(Description,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'') = '' then Name else case when Description IS NULL then Name else Description end end as FullName, case when Quorum = '0' then 'NĖRA KVORUMO' else case when Passed = '1' then 'PRIIMTAS' else 'NEPRIIMTAS' end end as VoteRes, 'UŽ   '+CAST(" + Configuration.rd_uz + " as varchar) as res1, 'SUSILAIKĖ   '+CAST(" + Configuration.rd_pries + " as varchar) as res3, 'PRIEŠ   '+CAST(" + Configuration.rd_susilaike + " as varchar) as res2, 'NEBALSAVO   '+CAST(VotingSession.Result5 as varchar) as res5, 'Užsiregistravo '+CAST(VotingSession.Result6 as varchar) as res6 from AgendaText left join VotingSession on VotingSession.Agenda_id=AgendaText.Agenda_id left join Agenda on Agenda.Id=AgendaText.Agenda_id where AgendaText.Agenda_id in (Select ID from Agenda where Conference_id=" + guid + " ) group by Agenda.SortOrder,Agenda.ItemNumber,AgendaText.Agenda_id, AgendaText.Name, AgendaText.Description,VotingSession.Passed,VotingSession.Quorum,VotingSession.Result1,VotingSession.Result2,VotingSession.Result3,VotingSession.Result5,VotingSession.Result6,VotingSession.Id order by VotingSession.Id asc";

                    DataTable tbagenda = db.getTable(sql);
                    //string sqldelegates = "select COUNT(*) from DelegateList where conference_id=" + guid;
                    string sqldelegates = "select distinct COUNT(distinct delegate_id) from LogDelegate where conference_id=" + guid;
                    DataTable tbdelegates = db.getTable(sqldelegates);
                    //  word.document.Paragraphs.Add(ref rng);
                    //  word.document.Paragraphs.Add(ref rng);
                    //  word.document.Paragraphs.Add(ref rng);
                    //  word.document.Paragraphs.Add(ref rng);



                    int tbcnt = 0;
                    Int64 nr = 0;
                    if (tbagenda.Rows.Count > 0)
                    {
                        Int64 row = 7;
                        for (int i = 0; i < tbagenda.Rows.Count; i++)
                        {
                            string paragraph = "";
                            string itemnumber = "";
                            try
                            {
                                itemnumber = tbagenda.Rows[i]["itemnr"].ToString();
                            }
                            catch (Exception ex) { }
                            string version = tbagenda.Rows[i]["vr"].ToString();
                            string name = tbagenda.Rows[i]["FullName"].ToString();
                            string status = tbagenda.Rows[i]["VoteRes"].ToString();
                            string res1 = tbagenda.Rows[i]["res1"].ToString();
                            string res2 = tbagenda.Rows[i]["res2"].ToString();
                            string res3 = tbagenda.Rows[i]["res3"].ToString();
                            string res5 = tbagenda.Rows[i]["res5"].ToString();
                            string res6 = tbagenda.Rows[i]["res6"].ToString();
                            string id = tbagenda.Rows[i]["id"].ToString();
                            string aid = tbagenda.Rows[i]["aid"].ToString();
                            string aid_next = "0";
                            try
                            {
                                aid_next = tbagenda.Rows[i + 1]["aid"].ToString();
                            }
                            catch (Exception ex) { aid_next = "0"; }
                            string sqlvs = "select * from VotingSession where conference_id=" + guid + " and id=" + id;
                            DataTable tbvotingsession = db.getTable(sqlvs);
                            if (tbvotingsession.Rows.Count > 0)
                            {

                                if (!aid.Equals(aid_next))
                                {

                                    string sqlres = "select distinct LogDelegate.delegate_id as subid,DelegateText.name,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote3=1 then 'Susilaikė' else case when VotingResult.Vote2=1 then 'Prieš' end end end as vt from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where LogDelegate.conference_id=" + guid + " and VotingSession.id=" + id + " ORDER BY DelegateText.name ASC";

                                    DataTable tbres = db.getTable(sqlres);

                                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Size = 16;
                                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Bold = 1;
                                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                    string votingparameter_id = tbvotingsession.Rows[0]["VotingParameter_id"].ToString();
                                    if (votingparameter_id.Equals("69"))
                                    {
                                        word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.InsertAfter("SLAPTO BALSAVIMO REZULTATŲ PROTOKOLAS");

                                    }
                                    else
                                    {
                                        word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.InsertAfter("ATVIRO BALSAVIMO REZULTATŲ PROTOKOLAS");
                                    }


                                    word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                    word.document.Paragraphs.Last.Range.InsertParagraphAfter();

                                    nr++;
                                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Size = 12;
                                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Bold = 0;
                                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                    word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.InsertAfter(name.Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ')); //int

                                    word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                    word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                    string nr_meet = "";
                                    string countdel = "";
                                    if (tbvotingsession.Rows.Count > 0)
                                    {
                                        //countdel = tbvotingsession.Rows[0]["TotalSeats"].ToString();
                                        countdel = Functionality.getNumber().ToString();
                                    }
                                    else
                                    {
                                        countdel = "0";
                                    }
                                    //countdel = tbres.Rows.Count.ToString();
                                    string col1row1 = meeting_title + nr_meet;
                                    string col2row1 = "";
                                    string col1row2 = "";
                                    if (tbvotingsession.Rows.Count > 0)
                                    {
                                        col2row1 = "Užsiregistravo      " + tbvotingsession.Rows[0]["TotalLoggedIn"].ToString();
                                        col1row2 = tbvotingsession.Rows[0]["Date"].ToString().Substring(0, 19);
                                    }
                                    else
                                    {
                                        col2row1 = "Užsiregistravo      0";
                                        col1row2 = "";
                                    }
                                    string col2row2 = "Viso balsavo        ";
                                    if (tbvotingsession.Rows.Count > 0)
                                    {
                                        col2row2 = col2row2 + tbvotingsession.Rows[0]["Result5"].ToString();  //col2row2 = col2row2 + tbvotingsession.Rows[0]["TotalVotes"].ToString();
                                    }
                                    else
                                    {
                                        col2row2 = col2row2 + "0";
                                    }
                                    string col1row3 = "Sąraše Tarybos narių: " + countdel;
                                    string col2row3 = res1 + "    " + res2 + "    " + res3.Replace("SUSILAIKE", "SUSILAIKĖ");
                                    Object defaultTableBehavior = Type.Missing;
                                    Object autoFitBehavior = Type.Missing;

                                    //word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                    //word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                    word.document.Paragraphs.Last.Range.Font.Size = 10;
                                    word.document.Paragraphs.Last.Range.Font.Bold = 0;
                                    word.document.Paragraphs.Last.Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                                    Word.Range rngw = word.document.Paragraphs.Last.Range;
                                    tbcnt++;
                                    word.document.Tables.Add(rngw, 3, 4, ref defaultTableBehavior, ref autoFitBehavior);
                                    word.document.Tables.Item(tbcnt).Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleNone;
                                    word.document.Tables.Item(tbcnt).Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleNone;
                                    word.document.Tables.Item(tbcnt).Cell(1, 2).Range.InsertAfter(col1row1);
                                    word.document.Tables.Item(tbcnt).Cell(1, 3).Range.InsertAfter(col2row1);
                                    word.document.Tables.Item(tbcnt).Cell(2, 2).Range.InsertAfter(col1row2);
                                    word.document.Tables.Item(tbcnt).Cell(2, 3).Range.InsertAfter(col2row2);
                                    word.document.Tables.Item(tbcnt).Cell(3, 2).Range.InsertAfter(col1row3);
                                    // word.document.Tables.Item(0).Cell(3, 2).Range.InsertAfter(col2row3);
                                    if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                                    {
                                        word.document.Tables.Item(tbcnt).Cell(3, 3).Range.InsertAfter("");
                                    }
                                    else
                                    {
                                        word.document.Tables.Item(tbcnt).Cell(3, 3).Range.InsertAfter(col2row3);
                                    }

                                    word.document.Tables.Item(tbcnt).Columns.Item(1).SetWidth(100, Word.WdRulerStyle.wdAdjustSameWidth);
                                    word.document.Tables.Item(tbcnt).Columns.Item(2).SetWidth(180, Word.WdRulerStyle.wdAdjustSameWidth);
                                    word.document.Tables.Item(tbcnt).Columns.Item(3).SetWidth(180, Word.WdRulerStyle.wdAdjustSameWidth);
                                    //word.document.Paragraphs.Last.Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                    word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                    // word.document.Paragraphs.Last.Range.InsertParagraphAfter();

                                    //string sqlres = "select ROW_NUMBER()OVER (order by DelegateText.name asc) as Nr,name,case when Vote IS NULL then '' else Vote end as vt from DelegateList left join DelegateText on DelegateList.delegate_id=DelegateText.delegate_id left join (select DelegateList.delegate_id as subid,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote3=1 then 'Susilaikė' else case when VotingResult.Vote2=1 then 'Prieš' end end end as Vote from DelegateList left join DelegateText on DelegateList.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where DelegateList.conference_id=" + guid + " and VotingSession.id=" + id + ") as tbs on DelegateList.delegate_id=tbs.subid where conference_id=" + guid + " order by name asc";
                                    if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                                    {
                                        word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Size = 10;
                                        word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Font.Bold = 0;
                                        word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;

                                        word.document.Paragraphs.Item(word.document.Paragraphs.Count).Range.InsertAfter("REGISTRAVOSI");
                                    }

                                    defaultTableBehavior = Type.Missing;
                                    autoFitBehavior = Type.Missing;
                                    rngw = word.document.Paragraphs.Last.Range;
                                    word.document.Paragraphs.Last.Range.Font.Size = 10;
                                    word.document.Paragraphs.Last.Range.Font.Bold = 0;
                                    word.document.Paragraphs.Last.Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;




                                    string sqlres1 = "select distinct LogDelegate.delegate_id as subid,DelegateText.name,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'UŽ' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'SUSILAIKĖ' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'PRIEŠ' end end end as vt from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where LogDelegate.conference_id=" + guid + " and VotingSession.id=" + id + " ORDER BY DelegateText.name ASC";
                                    DataTable tbres1 = db.getTable(sqlres1);
                                    if (tbres1.Rows.Count > 0)
                                    {
                                        tbres1 = Functionality.sortTableByName(tbres1);
                                        int rscnt = tbres1.Rows.Count;
                                        if (rscnt == 0)
                                        {
                                            rscnt = 1;
                                        }
                                        word.document.Tables.Add(rngw, rscnt, 3, ref defaultTableBehavior, ref autoFitBehavior);
                                        tbcnt++;
                                        Int64 yy = 0;
                                        for (int y = 0; y < tbres.Rows.Count; y++)
                                        {
                                            if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                                            {
                                                if (tbres1.Rows[y]["vt"].ToString().Contains("UŽ"))
                                                {
                                                    row++;
                                                    yy++;
                                                    word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
                                                    word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.InsertAfter((yy).ToString());//tbres.Rows[y]["Nr"].ToString());
                                                    word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                                                    word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.InsertAfter(tbres1.Rows[y]["name"].ToString());
                                                    word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                                                    word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.InsertAfter("");
                                                }
                                            }
                                            else
                                            {
                                                row++;
                                                word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
                                                word.document.Tables.Item(tbcnt).Cell(y + 1, 1).Range.InsertAfter((y + 1).ToString());//tbres.Rows[y]["Nr"].ToString());
                                                word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                                                word.document.Tables.Item(tbcnt).Cell(y + 1, 2).Range.InsertAfter(tbres1.Rows[y]["name"].ToString());
                                                word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.Paragraphs.Alignment = Word.WdParagraphAlignment.wdAlignParagraphLeft;
                                                word.document.Tables.Item(tbcnt).Cell(y + 1, 3).Range.InsertAfter(tbres1.Rows[y]["vt"].ToString().Replace("SUSILAIKE", "SUSILAIKĖ"));
                                            }

                                        }
                                    }
                                    word.document.Tables.Item(tbcnt).Columns.Item(1).SetWidth(170, Word.WdRulerStyle.wdAdjustSameWidth);
                                    word.document.Tables.Item(tbcnt).Columns.Item(2).SetWidth(150, Word.WdRulerStyle.wdAdjustSameWidth);
                                    word.document.Tables.Item(tbcnt).Columns.Item(3).SetWidth(80, Word.WdRulerStyle.wdAdjustSameWidth);

                                    word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                    word.document.Paragraphs.Last.Range.InsertParagraphAfter();
                                      object dir = Microsoft.Office.Interop.Word.WdCollapseDirection.wdCollapseEnd;
                                    word.document.Paragraphs.Last.Range.Collapse(ref dir);
                                    object tp = Microsoft.Office.Interop.Word.WdBreakType.wdPageBreak;
                                    word.document.Paragraphs.Last.Range.InsertBreak(ref tp);


                                }
                            }
                        }
                    }

                }
              //  db = null;
            }*/
               // object fileformat=Microsoft.Office.Interop.Word.WdDocumentType.
                apWord.Documents[1].SaveAs(name, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
                apWord.Documents[1].Close(ref miss, ref miss, ref miss);    
                apWord.Quit();
        }
/*
        public static void addToExcel(ExcelWrapper excel, string filename, string template, string meeting_title, string guid)
        {
            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel.Workbook wb = excel.GetActiveWorkbook("template.xls");
            object mis = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Worksheet sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;

            // sh.P



            if (Configuration.rd_img.ToUpper().Equals("TRUE") && (!Configuration.txt_img_path.Equals("")))
            {
                string file = Configuration.txt_img_path;
                try
                {
                    //sh.Shapes.AddPicture(file, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 10, 5, 300, 20);

                    string path = System.IO.Path.GetFullPath(file);
                    using (System.Drawing.Image i = System.Drawing.Image.FromFile(path))
                    {
                        Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)sh.Cells[1, 1];
                        Clipboard.SetImage(i);
                        sh.Paste(r, System.Reflection.Missing.Value);
                        sh.Shapes.Item(1).Height = 30;
                        // sh.Shapes.Item(1).Width = 716;
                    }
                }
                catch (Exception ex) { Console.WriteLine(ex.Message); }
            }
            if (Configuration.rd_text.ToUpper().Equals("TRUE") && (!Configuration.txt_text.Equals("")))
            {
                sh.Cells[2, 2] = Configuration.txt_text;
                //sh.get_Range(cel1.cel2).Se
            }

            if ((!meeting_title.Equals("")) && (!guid.Equals("")))
            {
                DataBase db = new DataBase(Configuration.dbServer, Configuration.db_user, Configuration.db_password, Configuration.db);

                //sena DataTable tb = db.getTable("select date,time from StoryStartTime_tbl where guid='" + guid + "'");
                string sqllt = "SELECT REPLACE(SUBSTRING(CONVERT(VARCHAR,starttime,102),0,11),'.','-') as datet,SUBSTRING(CONVERT(VARCHAR,starttime,8),0,9) as timet,REPLACE(SUBSTRING(CONVERT(VARCHAR,stoptime,102),0,11),'.','-') as dates,SUBSTRING(CONVERT(VARCHAR,stoptime,8),0,9) as times, REPLACE(SUBSTRING(CONVERT(VARCHAR,(stoptime-starttime),102),0,11),'.','-') as datel,SUBSTRING(CONVERT(VARCHAR,(stoptime-starttime),8),0,9) as timel from logConference where conference_id=" + guid + " order by timet asc";
                DataTable tb = db.getTable(sqllt);

                string startDate = "";
                string startTime = "";
                string stopDate = "";
                string stopTime = "";
                string lastingDate = "";
                string lastingTime = "";
                if (tb.Rows.Count > 0)
                {
                    startDate = tb.Rows[0]["datet"].ToString();
                    startTime = tb.Rows[0]["timet"].ToString();
                    stopDate = tb.Rows[tb.Rows.Count - 1]["dates"].ToString();
                    stopTime = tb.Rows[tb.Rows.Count - 1]["times"].ToString();
                    lastingDate = tb.Rows[0]["datel"].ToString();
                    lastingTime = Functionality.getDurationConference(tb);//tb.Rows[0]["timel"].ToString();
                }

                if (template.Equals("Vardinių balsavimų ataskaita"))
                {
                    sh.Cells[3, 2] = "Vardinių balsavimų ataskaita";
                    Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)sh.Cells[3, 2];

                    r.Font.Size = 20;

                    sh.Cells[5, 1] = "Posėdžio pavadinimas:" + meeting_title;
                    sh.Cells[6, 1] = "Posėdžio data: " + startDate + " " + startTime;

                    string sql = "SELECT  Agenda.SortOrder as so,Agenda.ItemNumber as itemnr,max(VotingSession.Version) as vr,VotingSession.Id as id, AgendaText.Agenda_id as aid, case when REPLACE(REPLACE(REPLACE(REPLACE(Description,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'') = '' then Name else case when Description IS NULL then Name else Description end end as FullName, case when Quorum = '0' then 'NĖRA KVORUMO' else case when Passed = '1' then 'PRIIMTAS' else 'NEPRIIMTAS' end end as VoteRes, 'UŽ - '+CAST(" + Configuration.rd_uz + " as varchar) as res1, 'SUSILAIKĖ - '+CAST(" + Configuration.rd_susilaike + " as varchar) as res3, 'PRIEŠ - '+CAST(" + Configuration.rd_pries + " as varchar) as res2, 'NEBALSAVO - '+CAST(VotingSession.Result5 as varchar) as res5, 'BALSAVIME DALYVAVO - '+CAST(VotingSession.Result6 as varchar) as res6 from AgendaText left join VotingSession on VotingSession.Agenda_id=AgendaText.Agenda_id left join Agenda on Agenda.Id=AgendaText.Agenda_id where AgendaText.Agenda_id in (Select ID from Agenda where Conference_id=" + guid + " ) group by Agenda.SortOrder,Agenda.ItemNumber,AgendaText.Agenda_id, AgendaText.Name, AgendaText.Description,VotingSession.Passed,VotingSession.Quorum,VotingSession.Result1,VotingSession.Result2,VotingSession.Result3,VotingSession.Result5,VotingSession.Result6,VotingSession.Id order by so asc";


                    DataTable tbagenda = db.getTable(sql);
                    int nr = 0;
                    if (tbagenda.Rows.Count > 0)
                    {
                        int row = 9;
                        for (int i = 0; i < tbagenda.Rows.Count; i++)
                        {

                            string paragraph = "";
                            string itemnumber = "";
                            try
                            {
                                itemnumber = tbagenda.Rows[i]["itemnr"].ToString();
                            }
                            catch (Exception ex) { }
                            string version = tbagenda.Rows[i]["vr"].ToString();
                            string name = tbagenda.Rows[i]["FullName"].ToString();
                            string status = tbagenda.Rows[i]["VoteRes"].ToString();
                            string res1 = tbagenda.Rows[i]["res1"].ToString();
                            string res2 = tbagenda.Rows[i]["res2"].ToString();
                            string res3 = tbagenda.Rows[i]["res3"].ToString();
                            string res5 = tbagenda.Rows[i]["res5"].ToString();
                            string res6 = tbagenda.Rows[i]["res6"].ToString();
                            string id = tbagenda.Rows[i]["id"].ToString();
                            string aid = tbagenda.Rows[i]["aid"].ToString();
                            string aid_next = "0";
                            try
                            {
                                aid_next = tbagenda.Rows[i + 1]["aid"].ToString();
                            }
                            catch (Exception ex) { aid_next = "0"; }
                            if (!aid.Equals(aid_next))
                            {
                                nr++;
                                sh.Cells[row, 1] = paragraph + itemnumber + " " + name.Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
                                row++;
                                row++;
                                sh.Cells[row, 1] = paragraph + res6;
                                row++;
                                sh.Cells[row, 1] = paragraph + res1;
                                row++;
                                sh.Cells[row, 1] = paragraph + res2;
                                row++;
                                sh.Cells[row, 1] = paragraph + res3.Replace("SUSILAIKE", "SUSILAIKĖ");
                                row++;
                                sh.Cells[row, 1] = paragraph + res5;
                                row++;
                                row++;

                                List<Functionality.DelegateObj> lsdt = new List<Functionality.DelegateObj>();
                                foreach (Functionality.DelegateObj dob in Functionality.lsd)
                                {
                                    lsdt.Add(dob);
                                }

                                if (res1.Equals("") && res2.Equals("") && res3.Equals("") && res5.Equals(""))
                                {
                                    sh.Cells[row, 1] = paragraph + "ŠIUO KLAUSIMU NEBUVO BALSUOJAMA";
                                }
                                else
                                {
                                    sh.Cells[row, 1] = paragraph + "KLAUSIMAS " + status.Replace("NERA", "NEPRIIMTAS - NĖRA");
                                }
                                row++;
                                row++;
                                string fieldname = "VotingResult.Vote1";
                                switch (Configuration.rd_uz)
                                {
                                    case "VotingSession.Result1": fieldname = "VotingResult.Vote1"; break;
                                    case "VotingSession.Result2": if (Configuration.rd_pries.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote3"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote3"; } break;
                                    case "VotingSession.Result3": if (Configuration.rd_pries.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } break;
                                }
                                string sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Už' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Susilaikė' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and " + fieldname + "=1 order by Vote,DelegateText.name";//Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote")
                                DataTable tbvotings = db.getTable(sql1);
                                if (tbvotings.Rows.Count > 0)
                                {
                                    sh.Cells[row, 1] = paragraph + "BALSAVO UŽ:";
                                    row++;
                                    for (int y = 0; y < tbvotings.Rows.Count; y++)
                                    {
                                        string votingname = tbvotings.Rows[y]["named"].ToString();
                                        Functionality.findInLsdAndRemove(lsdt, votingname);
                                        string vote = tbvotings.Rows[y]["Vote"].ToString();
                                        sh.Cells[row, 1] = "            " + votingname;
                                        row++;
                                    }
                                    row++;
                                }
                                fieldname = "VotingResult.Vote2";
                                switch (Configuration.rd_pries)
                                {
                                    case "VotingSession.Result1": if (Configuration.rd_uz.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote1"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote1"; } break;
                                    case "VotingSession.Result2": if (Configuration.rd_uz.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } else if (Configuration.rd_susilaike.Equals("VotingSession.Result1")) { fieldname = "VotingResult.Vote2"; } break;
                                    case "VotingSession.Result3": fieldname = "VotingResult.Vote3"; break;
                                }
                                sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Už' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Susilaikė' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and " + fieldname + "=1 order by Vote,DelegateText.name"; //Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote")
                                tbvotings = db.getTable(sql1);
                                if (tbvotings.Rows.Count > 0)
                                {
                                    sh.Cells[row, 1] = paragraph + "BALSAVO PRIEŠ:";
                                    row++;
                                    for (int y = 0; y < tbvotings.Rows.Count; y++)
                                    {
                                        string votingname = tbvotings.Rows[y]["named"].ToString();
                                        Functionality.findInLsdAndRemove(lsdt, votingname);
                                        string vote = tbvotings.Rows[y]["Vote"].ToString();
                                        sh.Cells[row, 1] = "            " + votingname;
                                        row++;
                                    }
                                    row++;
                                }
                                fieldname = "VotingResult.Vote3";
                                switch (Configuration.rd_susilaike)
                                {
                                    case "VotingSession.Result1": if (Configuration.rd_pries.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote1"; } else if (Configuration.rd_uz.Equals("VotingSession.Result3")) { fieldname = "VotingResult.Vote1"; } break;
                                    case "VotingSession.Result2": fieldname = "VotingResult.Vote2"; break;
                                    case "VotingSession.Result3": if (Configuration.rd_pries.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote2"; } else if (Configuration.rd_uz.Equals("VotingSession.Result2")) { fieldname = "VotingResult.Vote1"; } break;
                                }
                                sql1 = "select VotingSession.Id, VotingSession.Agenda_id,AgendaText.Name,DelegateText.name  as named,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Už' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Susilaikė' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'Prieš' end end end as Vote from VotingSession left join AgendaText on VotingSession.Agenda_id=AgendaText.Agenda_id left join VotingResult on VotingSession.id=VotingResult.VotingSession_id left join DelegateText on  VotingResult.LogDelegate_id=DelegateText.delegate_id where conference_id=" + guid + " and VotingSession.id=" + id + " and " + fieldname + "=1 order by Vote,DelegateText.name"; //Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote")

                                tbvotings = db.getTable(sql1);
                                if (tbvotings.Rows.Count > 0)
                                {
                                    sh.Cells[row, 1] = paragraph + "SUSILAIKĖ:";
                                    row++;
                                    for (int y = 0; y < tbvotings.Rows.Count; y++)
                                    {
                                        string votingname = tbvotings.Rows[y]["named"].ToString();
                                        Functionality.findInLsdAndRemove(lsdt, votingname);
                                        string vote = tbvotings.Rows[y]["Vote"].ToString();
                                        sh.Cells[row, 1] = "            " + votingname;
                                        row++;
                                    }
                                    row++;
                                }

                                if (lsdt.Count > 0)
                                {
                                    sh.Cells[row, 1] = paragraph + "NEBALSAVO:";
                                    row++;
                                    foreach (Functionality.DelegateObj dob in lsdt)
                                    {
                                        sh.Cells[row, 1] = "            " + dob.name;
                                        row++;
                                    }
                                    row++;
                                }

                                row++;

                            }
                        }
                    }

                }
                if (template.Equals("Posėdžio dalyvių ataskaita"))
                {
                    sh.Cells[3, 2] = "Posėdžio dalyvių ataskaita";
                    Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)sh.Cells[3, 2];
                    r.Font.Size = 20;

                    sh.Cells[5, 1] = "Posėdžio pavadinimas:" + meeting_title;
                    sh.Cells[6, 1] = "Posėdžio pradžia: " + startDate + " " + startTime;
                    sh.Cells[7, 1] = "Posėdžio pabaiga: " + stopDate + " " + stopTime;


                    if (Functionality.lsd.Count > 0)
                    {
                        int i = 0;
                        foreach (Functionality.DelegateObj dob in Functionality.lsd)
                        {
                            sh.Cells[i + 10, 1] = dob.nr + ") " + dob.name + "      " + dob.type;
                            i++;
                        }
                    }
          
                }
                if (template.Equals("Tarybos narių aktyvumo ataskaita"))
                {
                    sh.Cells[3, 2] = "Tarybos narių aktyvumo ataskaita";
                    Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)sh.Cells[3, 2];
                    r.Font.Size = 20;

                    sh.Cells[5, 1] = "Posėdžio pavadinimas:" + meeting_title;
                    sh.Cells[6, 1] = "Posėdžio data: " + startDate + " " + startTime;
                    sh.Cells[7, 1] = "Posėdžio pabaiga: " + stopDate + " " + stopTime;
                    sh.Cells[8, 1] = "Posėdžio trukmė: " + lastingTime;

                    //sena DataTable tbagenda = db.getTable("SELECT * FROM DelegateInfoFinal_tbl where guids='" + guid + "' order by delegate_id asc");
                    string sql = "SELECT distinct DelegateText.name as name,LogDelegate.delegate_id as del_id from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id where conference_id=" + guid + "and (UserTypeText.name='Tarybos narys' or UserTypeText.name='Pirmininkaujantis')";
                    //sena2 DataTable tbagenda = db.getTable(sql);
                    DataTable tbagenda = db.getTable("SELECT distinct DelegateText.name as name, UserTypeText.name as type, rank() OVER (ORDER BY DelegateText.name) as nr,LogDelegate.delegate_id as del_id from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id left join Delegate on Delegate.id=LogDelegate.delegate_id left join UserTypeText on UserTypeText.usertype_id=Delegate.usertype_id where conference_id=" + guid + " and (UserTypeText.name='Tarybos narys' or UserTypeText.name='Pirmininkaujantis') group by DelegateText.name,UserTypeText.name,LogDelegate.delegate_id ");

                    if (tbagenda.Rows.Count > 0)
                    {
                        //sh.Cells[8, 1] = "Dalyvavusių posėdyje tarybos narių sąrašas:";

                        int row = 11;
                        for (int i = 0; i < tbagenda.Rows.Count; i++)
                        {
                            string delegate_id = tbagenda.Rows[i]["del_id"].ToString();
                            sql = "SELECT DelegateText.name,LogSpeak.startTime,LogSpeak.stopTime,CAST((LogSpeak.stopTime - LogSpeak.startTime) as time(7)) AS Duration from logSpeak left join DelegateText on LogSpeak.delegate_id=DelegateText.delegate_id where conference_id=" + guid + " and LogSpeak.delegate_id=" + delegate_id + " order by LogSpeak.startTime asc;";
                            DataTable tbdelegate = db.getTable(sql);
                            TimeSpan durat = TimeSpan.Parse("00:00:00");
                            if (tbdelegate.Rows.Count > 0)
                            {
                                for (int y = 0; y < tbdelegate.Rows.Count; y++)
                                {
                                    try
                                    {
                                        string dur = tbdelegate.Rows[y]["Duration"].ToString();
                                        TimeSpan currdur = TimeSpan.Parse(dur);
                                        durat = durat + currdur;
                                    }
                                    catch (Exception ex) { }
                                }
                            }

                            int qcount = 0;
                            int qqcount = 0;
                            sql = "SELECT AgendaText.Name,logSubject.starttime,logSubject.stoptime from logSubject left join AgendaText on logSubject.agenda_id=AgendaText.Agenda_id where conference_id=" + guid + " order by logSubject.starttime asc;";
                            DataTable tbquestions = db.getTable(sql);
                            if (tbquestions.Rows.Count > 0)
                            {
                                for (int pp = 0; pp < tbquestions.Rows.Count; pp++)
                                {
                                    try
                                    {
                                        CultureInfo myCIint2 = new CultureInfo("lt-LT");
                                        System.Threading.Thread.CurrentThread.CurrentCulture = myCIint2;
                                        string tm1 = tbquestions.Rows[pp]["starttime"].ToString().Substring(11, 8);
                                        string tm2 = tbquestions.Rows[pp]["stoptime"].ToString().Substring(11, 8);
                                        TimeSpan questionstart = TimeSpan.Parse(tm1);
                                        TimeSpan questionend = TimeSpan.Parse(tm2);
                                        myCIint2 = new CultureInfo("en-US");
                                        System.Threading.Thread.CurrentThread.CurrentCulture = myCIint2;
                                        sql = "SELECT DelegateText.name,LogSpeak.startTime,LogSpeak.stopTime,CAST((LogSpeak.stopTime - LogSpeak.startTime) as time(7)) AS Duration from logSpeak left join DelegateText on LogSpeak.delegate_id=DelegateText.delegate_id where conference_id=" + guid + " and LogSpeak.delegate_id=" + delegate_id + " order by LogSpeak.startTime asc;";
                                        DataTable tbdelegatet = db.getTable(sql);
                                        if (tbdelegatet.Rows.Count > 0)
                                        {
                                            qcount = 0;
                                            for (int y = 0; y < tbdelegatet.Rows.Count; y++)
                                            {
                                                CultureInfo myCIint3 = new CultureInfo("lt-LT");
                                                System.Threading.Thread.CurrentThread.CurrentCulture = myCIint3;
                                                TimeSpan delegatestart = TimeSpan.Parse(tbdelegatet.Rows[y]["startTime"].ToString().Substring(11, 8));
                                                TimeSpan delegateend = TimeSpan.Parse(tbdelegatet.Rows[y]["stopTime"].ToString().Substring(11, 8));
                                                myCIint3 = new CultureInfo("en-US");
                                                System.Threading.Thread.CurrentThread.CurrentCulture = myCIint3;
                                                if ((questionstart <= delegatestart) && (delegateend <= questionend))
                                                {
                                                    qcount++;
                                                }
                                            }
                                            if (qcount > 0) { qqcount++; }
                                        }
                                    }
                                    catch (Exception ex) { }
                                }
                            }
                            string name = tbagenda.Rows[i]["name"].ToString();
                            string nr = tbagenda.Rows[i]["nr"].ToString();
                            string countquestion = qqcount.ToString();
                            string countspeak = tbdelegate.Rows.Count.ToString();
                            string duration = durat.ToString();// durat.Hours.ToString() + ":" + durat.Minutes.ToString()+":"+durat.Seconds.ToString();
                            //string delegatestring = string.Format("{0} aktyvumo duomenys – dalyvavo [{1}] klausimų svarstyme, pasisakė [{2}] kartų, bendra pasisakymų trukmė [{3}] ", name, countquestion, countspeak, duration);
                            string delegatestring = string.Format("{0}) {1} ", nr, name);//– dalyvavo [{1}] klausimų svarstyme, pasisakė [{2}] kartų, bendra pasisakymų trukmė [{3}] ",nr,name,countquestion,countspeak,duration);
                            try
                            {
                                sh.Cells[row, 1] = delegatestring;
                                row++;
                            }
                            catch (Exception ex)
                            {
                                //MessageBox.Show(ex.Message);
                            }

                            DataTable vote_t = db.getTable("select count(*) from VotingResult left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where VotingSession.Conference_id=" + guid + " and VotingResult.LogDelegate_id=" + delegate_id);
                            if (vote_t.Rows.Count > 0)
                            {
                                string voten = vote_t.Rows[0][0].ToString();
                                string votingNr = string.Format("Balsavo [{0}] klausimų svarstyme", voten);
                                sh.Cells[row, 1] = "    " + votingNr;
                                row++;
                            }
                            string dlgestring = string.Format("Pasisakė [{0}] kartų, bendra pasisakymų trukmė [{1}] ", countspeak, duration);
                            sh.Cells[row, 1] = "    " + dlgestring;
                            row++;
                            sql = "select * from LogDelegate where conference_id=" + guid + " and delegate_id=" + delegate_id + " order by loginTime asc";
                            DataTable durt_t = db.getTable(sql);

                            sql = "SELECT max(stoptime),max(starttime) from logConference where conference_id=" + guid;
                            DataTable cnfend = db.getTable(sql);
                            string durr = Functionality.getDuration(durt_t, cnfend.Rows[0][0].ToString(), cnfend.Rows[0][1].ToString());


                            if (durt_t.Rows.Count > 0)
                            {
                                string durt = "00:00:00";
                                try
                                {
                                    // durt = durt_t.Rows[0][0].ToString().Substring(0, 8);
                                    durt = durr;
                                }
                                catch (Exception ex) { }
                                string activityduration = string.Format("Dalyvavimo posėdyje trukmė [{0}]", durt);
                                sh.Cells[row, 1] = "    " + activityduration;
                                row++;
                            }



                            row++;
                        }
                    }
             
                }
                if (template.Equals("Atviro balsavimo rezultatų protokolas"))
                {
                    int wscnt = 0;
                     sh.Cells[1, 1] = "";
                    sh.Cells[2, 2] = "";

                    try
                    {
                        if (sh.Shapes.Count > 0)
                        {
                            sh.Shapes.Item(1).Delete();
                        }
                    }
                    catch (Exception ex) { }
                    string sql = "SELECT  Agenda.SortOrder as so,Agenda.ItemNumber as itemnr,max(VotingSession.Version) as vr,VotingSession.Id as id, AgendaText.Agenda_id as aid, case when REPLACE(REPLACE(REPLACE(REPLACE(Description,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'') = '' then Name else case when Description IS NULL then Name else Description end end as FullName, case when Quorum = '0' then 'NĖRA KVORUMO' else case when Passed = '1' then 'PRIIMTAS' else 'NEPRIIMTAS' end end as VoteRes, 'UŽ   '+CAST(" + Configuration.rd_uz + " as varchar) as res1, 'SUSILAIKĖ   '+CAST(" + Configuration.rd_pries + " as varchar) as res3, 'PRIEŠ   '+CAST(" + Configuration.rd_susilaike + " as varchar) as res2, 'NEBALSAVO   '+CAST(VotingSession.Result5 as varchar) as res5, 'Užsiregistravo '+CAST(VotingSession.Result6 as varchar) as res6 from AgendaText left join VotingSession on VotingSession.Agenda_id=AgendaText.Agenda_id left join Agenda on Agenda.Id=AgendaText.Agenda_id where AgendaText.Agenda_id in (Select ID from Agenda where Conference_id=" + guid + " ) group by Agenda.SortOrder,Agenda.ItemNumber,AgendaText.Agenda_id, AgendaText.Name, AgendaText.Description,VotingSession.Passed,VotingSession.Quorum,VotingSession.Result1,VotingSession.Result2,VotingSession.Result3,VotingSession.Result5,VotingSession.Result6,VotingSession.Id order by VotingSession.Id asc";


                    DataTable tbagenda = db.getTable(sql);
                    int nr = 0;
                    //string sqldelegates = "select COUNT(*) from DelegateList where conference_id=" + guid;
                    string sqldelegates = "select distinct COUNT(distinct delegate_id) from LogDelegate where conference_id=" + guid;
                    DataTable tbdelegates = db.getTable(sqldelegates);
                    if (tbagenda.Rows.Count > 0)
                    {

                        for (int i = 0; i < tbagenda.Rows.Count; i++)
                        {
                            int row = 5;


                            string paragraph = "";
                            string itemnumber = "";
                            try
                            {
                                itemnumber = tbagenda.Rows[i]["itemnr"].ToString();
                            }
                            catch (Exception ex) { }
                            string version = tbagenda.Rows[i]["vr"].ToString();
                            string name = tbagenda.Rows[i]["FullName"].ToString();
                            string status = tbagenda.Rows[i]["VoteRes"].ToString();
                            string res1 = tbagenda.Rows[i]["res1"].ToString();
                            string res2 = tbagenda.Rows[i]["res2"].ToString();
                            string res3 = tbagenda.Rows[i]["res3"].ToString();
                            string res5 = tbagenda.Rows[i]["res5"].ToString();
                            string res6 = tbagenda.Rows[i]["res6"].ToString();
                            string id = tbagenda.Rows[i]["id"].ToString();
                            string aid = tbagenda.Rows[i]["aid"].ToString();
                            string aid_next = "0";
                            try
                            {
                                aid_next = tbagenda.Rows[i + 1]["aid"].ToString();
                            }
                            catch (Exception ex) { aid_next = "0"; }
                            string sqlvs = "select * from VotingSession where conference_id=" + guid + " and id=" + id;
                            DataTable tbvotingsession = db.getTable(sqlvs);
                            if (tbvotingsession.Rows.Count > 0)
                            {

                                if (!aid.Equals(aid_next))
                                {

                                    string sqlres = "select distinct LogDelegate.delegate_id as subid,DelegateText.name,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote3=1 then 'Susilaikė' else case when VotingResult.Vote2=1 then 'Prieš' end end end as vt from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where LogDelegate.conference_id=" + guid + " and VotingSession.id=" + id + " ORDER BY DelegateText.name ASC";
                                    DataTable tbres = db.getTable(sqlres);
                                    try
                                    {
                                        sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[wscnt + 1];
                                        wscnt++;
                                    }
                                    catch (Exception ex)
                                    {
                                        wb.Sheets.Add(Type.Missing, (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[wscnt], Type.Missing, Type.Missing);
                                        sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[wscnt + 1];
                                        wscnt++;
                                    }

                                    string votingparameter_id = tbvotingsession.Rows[0]["VotingParameter_id"].ToString();
                                    if (votingparameter_id.Equals("69"))
                                    {
                                        sh.Cells[3, 3] = "SLAPTO BALSAVIMO REZULTATŲ PROTOKOLAS";

                                    }
                                    else
                                    {
                                        sh.Cells[3, 3] = "ATVIRO BALSAVIMO REZULTATŲ PROTOKOLAS";
                                    }

                                    Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)sh.Cells[3, 2];
                                    r.Font.Size = 20;


                                    nr++;
                                    sh.Cells[row, 2] = name.Replace('\r', ' ').Replace('\n', ' ').Replace('\t', ' ');
                                    row++;
                                    row++;


                                    string nr_meet = "";
                                    string countdel = "";
                                    if (tbvotingsession.Rows.Count > 0)
                                    {
                                        //countdel = tbvotingsession.Rows[0]["TotalSeats"].ToString();
                                        countdel = Functionality.getNumber().ToString();
                                    }
                                    else
                                    {
                                        countdel = "0";
                                    }
                                    //countdel = tbres.Rows.Count.ToString();
                                    string col1row1 = meeting_title + nr_meet;
                                    string col2row1 = "";
                                    string col1row2 = "";
                                    if (tbvotingsession.Rows.Count > 0)
                                    {
                                        col2row1 = "Užsiregistravo      " + tbvotingsession.Rows[0]["TotalLoggedIn"].ToString();
                                        col1row2 = tbvotingsession.Rows[0]["Date"].ToString().Substring(0, 19);
                                    }
                                    else
                                    {
                                        col2row1 = "Užsiregistravo      0";
                                        col1row2 = "";
                                    }
                                    string col2row2 = "Viso balsavo        ";
                                    if (tbvotingsession.Rows.Count > 0)
                                    {
                                        col2row2 = col2row2 + tbvotingsession.Rows[0]["Result5"].ToString();  //col2row2 = col2row2 + tbvotingsession.Rows[0]["TotalVotes"].ToString();
                                    }
                                    else
                                    {
                                        col2row2 = col2row2 + "0";
                                    }
                                    string col1row3 = "Sąraše Tarybos narių: " + countdel;
                                    string col2row3 = res1 + "    " + res2 + "    " + res3.Replace("SUSILAIKE", "SUSILAIKĖ");

                                    sh.Cells[row, 2] = col1row1;
                                    sh.Cells[row, 6] = col2row1;
                                    row++;
                                    sh.Cells[row, 2] = col1row2.Split(' ')[0];
                                    //sh.Columns[row,2].
                                    r = (Microsoft.Office.Interop.Excel.Range)sh.Cells[row, 2];
                                    r.ColumnWidth = 12;

                                    sh.Cells[row, 3] = col1row2.Split(' ')[1];
                                    sh.Cells[row, 6] = col2row2;
                                    row++;
                                    sh.Cells[row, 2] = col1row3;
                                    sh.Cells[row, 6] = col2row3;
                                    if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                                    {
                                        sh.Cells[row, 6] = "";
                                    }
                                    else
                                    {
                                        sh.Cells[row, 6] = col2row3;
                                    }
                               
                                    row++;
                                    row++;
                                    //string sqlres = "select ROW_NUMBER()OVER (order by DelegateText.name asc) as Nr,name,case when Vote IS NULL then '' else Vote end as vt from DelegateList left join DelegateText on DelegateList.delegate_id=DelegateText.delegate_id left join (select DelegateList.delegate_id as subid,case when VotingResult.Vote1=1 then 'Už' else case when VotingResult.Vote3=1 then 'Susilaikė' else case when VotingResult.Vote2=1 then 'Prieš' end end end as Vote from DelegateList left join DelegateText on DelegateList.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where DelegateList.conference_id=" + guid + " and VotingSession.id=" + id + ") as tbs on DelegateList.delegate_id=tbs.subid where conference_id=" + guid + " order by name asc";
                                    if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                                    {
                                        sh.Cells[row, 2] = "REGISTRAVOSI";

                                        row++;
                                        row++;
                                    }

                                    string sqlres1 = "select distinct LogDelegate.delegate_id as subid,DelegateText.name,case when " + Configuration.rd_uz.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'UŽ' else case when " + Configuration.rd_susilaike.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'SUSILAIKĖ' else case when " + Configuration.rd_pries.Replace("VotingSession.Result", "VotingResult.Vote") + "=1 then 'PRIEŠ' end end end as vt from LogDelegate left join DelegateText on LogDelegate.delegate_id=DelegateText.delegate_id left join VotingResult on  VotingResult.LogDelegate_id=DelegateText.delegate_id left join VotingSession on VotingResult.VotingSession_id=VotingSession.Id where LogDelegate.conference_id=" + guid + " and VotingSession.id=" + id + " ORDER BY DelegateText.name ASC";
                                    DataTable tbres1 = db.getTable(sqlres1);
                                    if (tbres1.Rows.Count > 0)
                                    {
                                        tbres1 = Functionality.sortTableByName(tbres1);
                                        int yy = 0;
                                        for (int y = 0; y < tbres1.Rows.Count; y++)
                                        {

                                            if (name.ToUpper().Contains("KVORUMO PATIKRINIMAS"))
                                            {
                                                if (tbres1.Rows[y]["vt"].ToString().Contains("UŽ"))
                                                {
                                                    row++;
                                                    yy++;
                                                    sh.Cells[row, 3] = (yy).ToString(); // tbres.Rows[y]["Nr"].ToString();
                                                    sh.Cells[row, 4] = tbres1.Rows[y]["name"].ToString();
                                                    sh.Cells[row, 7] = "";
                                                }
                                            }
                                            else
                                            {
                                                row++;
                                                sh.Cells[row, 3] = (y + 1).ToString(); // tbres.Rows[y]["Nr"].ToString();
                                                sh.Cells[row, 4] = tbres1.Rows[y]["name"].ToString();
                                                sh.Cells[row, 7] = tbres1.Rows[y]["vt"].ToString().Replace("SUSILAIKE", "SUSILAIKĖ");
                                            }

                                        }
                                    }

                                    row++;
                                    row++;

                                    // sh.Cells[row, 2] = "Posėdžio pirmininkas:";
                                    row++;
                                    row++;
                              
                                    row++;
                                }
                            }
                        }
                        sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
                        sh.Activate();
                    }

                }
                db = null;
            }

            sh = null;
            wb = null;


            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
        }*/





        public static void exportToWordProgram(string name, DateTime currDate)
        {
            if (getOfficeVersion().Equals("2000"))
            {
                exportToWordProgramOld(name, currDate);
            }
            else
            {
                exportToWordProgramNewer(name, currDate);
            }
        }

        public static void exportToWordProgramOld(string name, DateTime currDate)
        {
        }

        public static void exportToWordProgramNewer(string name, DateTime currDate)
        {

            DateTime fd = Functionality.GetFirstDateOfWeek(currDate, DayOfWeek.Monday);
            DateTime ld = fd + TimeSpan.FromDays(6);

            Microsoft.Office.Interop.Word.Application apWord = new Microsoft.Office.Interop.Word.Application();
            apWord.Visible = false;
            object miss=Type.Missing;
            object vis = true;
            apWord.Documents.Add(ref miss, ref miss, ref miss, ref vis);
            apWord.Documents[1].PageSetup.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA4;
   
            //apWord.Documents[1].PageSetup.PaperSize = Microsoft.Office.Interop.Word.WdPaperSize.wdPaperA3;
            apWord.Documents[1].PageSetup.Orientation = Microsoft.Office.Interop.Word.WdOrientation.wdOrientPortrait;
            //apWord.Documents[1].ActiveWindow.View.Type = Microsoft.Office.Interop.Word.WdViewType.wdOutlineView;
            apWord.Documents[1].ActiveWindow.View.TableGridlines = true;
            //apWord.Documents[0].ActiveWindow.View.Zoom.Percentage = 50;
            apWord.Documents[1].Paragraphs.LineSpacing = 15;
            object nullobj = System.Reflection.Missing.Value;
            object linktofile = false;
            object savewithfiles = true;
            object start1 = 1;
            object end1 = 0;

            try
            {
                //string file = Pdf.AppPath + "\\Lrytas_tv.png";
                string file = "";
                string path = System.IO.Path.GetFullPath(file);
                start1 = 0;
                object rng = apWord.Documents[1].Paragraphs.Last.Range;
                apWord.Documents[1].Paragraphs.Add(ref rng);
                apWord.Documents[1].Paragraphs.Add(ref rng);
                apWord.Documents[1].Paragraphs.Add(ref rng);
                apWord.Documents[1].Paragraphs.Add(ref rng);
                apWord.Documents[1].Paragraphs.Add(ref rng);
                apWord.Documents[1].Paragraphs.Add(ref rng);
                apWord.Documents[1].Paragraphs[1].Range.InlineShapes.AddPicture(path, ref linktofile, ref savewithfiles, ref rng);
                apWord.Documents[1].Paragraphs[1].Range.InlineShapes[1].Height = 30;
                apWord.Documents[1].Paragraphs[1].Range.InlineShapes[1].Width = 100;//716;
                apWord.Documents[1].Paragraphs[1].Range.Paragraphs.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;


                start1 = 0;
                object rngg = apWord.Documents[1].Paragraphs.Last.Range;
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs.Add(ref rngg);
                apWord.Documents[1].Paragraphs[2].Range.Font.Size = 16;
                apWord.Documents[1].Paragraphs[2].Range.Font.Bold = 1;
                apWord.Documents[1].Paragraphs[2].Range.Paragraphs.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;

                apWord.Documents[1].Paragraphs[2].Range.InsertBefore("Programa " + fd.ToShortDateString() + " - " + ld.ToShortDateString());

                apWord.Documents[1].Paragraphs[4].Range.Paragraphs.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphLeft;
                Functionality.LoadAppointmentsByDate(fd, ld);

                int parcnt = 4;
           bool[] labelday = new bool[7]{false,false,false,false,false,false,false};
           foreach (Appointment ap in Functionality.appointments)
           {
               if (ap.StartTime.ToShortDateString().Equals(fd.ToShortDateString()) && !labelday[0])
               {
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore(fd.ToShortDateString().Replace('.',' ')+" Pirmadienis");
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 14;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 1;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Underline = Microsoft.Office.Interop.Word.WdUnderline.wdUnderlineSingle;
                   parcnt++;
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   labelday[0] = true;
               }
               if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(1)).ToShortDateString()) && !labelday[1])
               {
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore((fd + TimeSpan.FromDays(1)).ToShortDateString().Replace('.', ' ') + " Antradienis");
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 14;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 1;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Underline = Microsoft.Office.Interop.Word.WdUnderline.wdUnderlineSingle;
                   parcnt++;
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   labelday[1] = true;
               }
               if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(2)).ToShortDateString()) && !labelday[2])
               {
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore((fd + TimeSpan.FromDays(2)).ToShortDateString().Replace('.', ' ') + " Trečiadienis");
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 14;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 1;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Underline = Microsoft.Office.Interop.Word.WdUnderline.wdUnderlineSingle;
                   parcnt++;
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   labelday[2] = true;
               }
               if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(3)).ToShortDateString()) && !labelday[3])
               {
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore((fd + TimeSpan.FromDays(3)).ToShortDateString().Replace('.', ' ') + " Ketvirtadienis");
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 14;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 1;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Underline = Microsoft.Office.Interop.Word.WdUnderline.wdUnderlineSingle;
                   parcnt++;
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   labelday[3] = true;
               }
               if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(4)).ToShortDateString()) && !labelday[4])
               {
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore((fd + TimeSpan.FromDays(4)).ToShortDateString().Replace('.', ' ') + " Penktadienis");
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 14;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 1;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Underline = Microsoft.Office.Interop.Word.WdUnderline.wdUnderlineSingle;
                   parcnt++;
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   labelday[4] = true;
               }
               if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(5)).ToShortDateString()) && !labelday[5])
               {
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore((fd + TimeSpan.FromDays(5)).ToShortDateString().Replace('.', ' ') + " Šeštadienis");
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 14;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 1;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Underline = Microsoft.Office.Interop.Word.WdUnderline.wdUnderlineSingle;
                   parcnt++;
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   labelday[5] = true;
               }
               if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(6)).ToShortDateString()) && !labelday[6])
               {
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore((fd + TimeSpan.FromDays(6)).ToShortDateString().Replace('.', ' ') + " Sekmadienis");
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 14;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 1;
                   apWord.Documents[1].Paragraphs[parcnt].Range.Font.Underline = Microsoft.Office.Interop.Word.WdUnderline.wdUnderlineSingle;
                   parcnt++;
                   rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
                   parcnt++;
                   apWord.Documents[1].Paragraphs.Add(ref rngg);
                   labelday[6] = true;
               }

               string dataSubject = "";
               if (ap.Location != "null")
               {
                    dataSubject = Functionality.getProgramDateFromDB(ap.Location,ap.Subject.Replace("\r\n",""))+".";
               }else{
                   dataSubject = " "+ap.Subject.Replace("\r\n","")+".";
               }
               dataSubject = dataSubject.Replace("..", ".");
              
               rngg = apWord.Documents[1].Paragraphs[parcnt].Range;
               apWord.Documents[1].Paragraphs.Add(ref rngg);
               apWord.Documents[1].Paragraphs[parcnt].Range.InsertBefore(ap.StartTime.ToShortTimeString().Substring(0, 5).Replace(':','.')  + dataSubject);
               apWord.Documents[1].Paragraphs[parcnt].Range.Font.Size = 12;
               apWord.Documents[1].Paragraphs[parcnt].Range.Font.Bold = 0;
               parcnt++;
              
           }
            }
            catch (Exception ex) { } 
            apWord.Documents[1].SaveAs(name, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            apWord.Documents[1].Close(ref miss, ref miss, ref miss);    
            apWord.Quit();
        }

        public static void exportToExcelPrograma(string filename, DateTime dateTime)
        {
            if (getOfficeVersion().Equals("2000"))
            {
                exportToExcelProgramaOld(filename, dateTime);
            }
            else
            {
                exportToExcelProgramaNewer(filename, dateTime);
            }
        }

        private static void exportToExcelProgramaNewer(string filename, DateTime dateTime)
        {
            DateTime fd = Functionality.GetFirstDateOfWeek(dateTime, DayOfWeek.Monday);
            DateTime ld = fd + TimeSpan.FromDays(6);

            CultureInfo myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            Microsoft.Office.Interop.Excel._Workbook oWBold = null;
            Microsoft.Office.Interop.Excel._Worksheet oSheetold = null;

            Microsoft.Office.Interop.Excel.Application oXLold = new Microsoft.Office.Interop.Excel.Application();
            oXLold.Visible = false;


            oWBold = (Microsoft.Office.Interop.Excel._Workbook)(oXLold.Workbooks.Open(AppPath + "\\tempPrograma.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
            oSheetold = (Microsoft.Office.Interop.Excel._Worksheet)oWBold.ActiveSheet;

            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
            oSheetold.get_Range("B11", "B11").Value = "Pirmadienis "+fd.ToShortDateString().Replace('.',' ');
            oSheetold.get_Range("C11", "C11").Value = "Antradienis " + (fd + TimeSpan.FromDays(1)).ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("D11", "D11").Value = "Trečiadienis " + (fd + TimeSpan.FromDays(2)).ToShortDateString().Replace('.', ' '); 
            oSheetold.get_Range("E11", "E11").Value = "Ketvirtadienis " + (fd + TimeSpan.FromDays(3)).ToShortDateString().Replace('.', ' '); 
            oSheetold.get_Range("F11", "F11").Value = "Penktadienis " + (fd + TimeSpan.FromDays(4)).ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("J11", "J11").Value = "Šeštadienis " + (fd + TimeSpan.FromDays(5)).ToShortDateString().Replace('.', ' '); 
            oSheetold.get_Range("K11", "K11").Value = "Sekmadienis " + (fd + TimeSpan.FromDays(6)).ToShortDateString().Replace('.', ' ');

            oSheetold.get_Range("B92", "B92").Value = "Pirmadienis " + fd.ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("C92", "C92").Value = "Antradienis " + (fd + TimeSpan.FromDays(1)).ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("D92", "D92").Value = "Trečiadienis " + (fd + TimeSpan.FromDays(2)).ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("E92", "E92").Value = "Ketvirtadienis " + (fd + TimeSpan.FromDays(3)).ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("F92", "F92").Value = "Penktadienis " + (fd + TimeSpan.FromDays(4)).ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("J92", "J92").Value = "Šeštadienis " + (fd + TimeSpan.FromDays(5)).ToShortDateString().Replace('.', ' ');
            oSheetold.get_Range("K92", "K92").Value = "Sekmadienis " + (fd + TimeSpan.FromDays(6)).ToShortDateString().Replace('.', ' ');

           // if (fd.Month==ld.Month){
            string mnf = "";
            switch (fd.Month)
            {
                case 1: mnf = "Sausio"; break;
                case 2: mnf = "Vasario"; break;
                case 3: mnf = "Kovo"; break;
                case 4: mnf = "Balandžio"; break;
                case 5: mnf = "Gegužės"; break;
                case 6: mnf = "Birželio"; break;
                case 7: mnf = "Liepos"; break;
                case 8: mnf = "Rugpjūčio"; break;
                case 9: mnf = "Rugsėjo"; break;
                case 10: mnf = "Spalio"; break;
                case 11: mnf = "Lapkričio"; break;
                case 12: mnf = "Gruodžio"; break;
                default: break;
            }
            string mnl = "";
            switch (ld.Month)
            {
                case 1: mnl = "Sausio"; break;
                case 2: mnl = "Vasario"; break;
                case 3: mnl = "Kovo"; break;
                case 4: mnl = "Balandžio"; break;
                case 5: mnl = "Gegužės"; break;
                case 6: mnl = "Birželio"; break;
                case 7: mnl = "Liepos"; break;
                case 8: mnl = "Rugpjūčio"; break;
                case 9: mnl = "Rugsėjo"; break;
                case 10: mnl = "Spalio"; break;
                case 11: mnl = "Lapkričio"; break;
                case 12: mnl = "Gruodžio"; break;
                default: break;
            }
          
            string mnd="";
            if (mnf.Equals(mnl))
            {
                mnd = mnf + " mėn. " + fd.Day + "-" + ld.Day+" d.";
            }
            else
            {
                mnd = mnf + " mėn. " + fd.Day + " d." + "-" + mnl + " mėn. " + ld.Day + " d.";
            }
            DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;
            System.Globalization.Calendar cal=dfi.Calendar;
            int sav = cal.GetWeekOfYear(fd, dfi.CalendarWeekRule, dfi.FirstDayOfWeek)-1;
            oSheetold.get_Range("B9", "B9").Value = sav+"sav. "+fd.Year+"m. "+mnd;
            oSheetold.get_Range("I94", "I94").Value = sav + "sav. " + fd.Year + "m. " + mnd;


            Functionality.LoadAppointmentsByDate(fd, ld);

            bool[] labelday = new bool[7] { false, false, false, false, false, false, false };
            string cell = "B";
            int i = 0;
            long laststart = -1;
            long lastlength = 0;
            long nextpos = 0;
            foreach (Appointment ap in Functionality.appointments)
            {
                if (ap.StartTime.ToShortDateString().Equals(fd.ToShortDateString()) && !labelday[0])
                {
                    cell = "B";
                    labelday[0] = true;
                }
                if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(1)).ToShortDateString()) && !labelday[1])
                {
                    cell = "C";
                    labelday[1] = true;
                }
                if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(2)).ToShortDateString()) && !labelday[2])
                {
                    cell = "D";
                    labelday[2] = true;
                }
                if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(3)).ToShortDateString()) && !labelday[3])
                {
                    cell = "E";
                   labelday[3] = true;
                }
                if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(4)).ToShortDateString()) && !labelday[4])
                {
                    cell = "F";
                    labelday[4] = true;
                }
                if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(5)).ToShortDateString()) && !labelday[5])
                {
                    cell = "J";
                    labelday[5] = true;
                }
                if (ap.StartTime.ToShortDateString().Equals((fd + TimeSpan.FromDays(6)).ToShortDateString()) && !labelday[6])
                {
                    cell = "K";
                    labelday[6] = true;
                }

                long si = getCell(ap.StartTime);

              /*  if ((si <= laststart + lastlength) && (laststart + lastlength>2)) { 
                    si = laststart + lastlength + 1; 
                }*/
                if ((si <= laststart) && (laststart-si<10)) { si = laststart + 1; }
               // if ((si <= nextpos) && (nextpos - si < 10)) { si = nextpos + 1; }
                
                long l = getCellLength(ap.StartTime, ap.EndTime);
                nextpos = si;
                string datafromDB = Functionality.getTinklelismDateFromDB(ap.Location,ap.Subject);
                oSheetold.get_Range(cell + (si + 11).ToString(), cell + (si + 11).ToString()).Value = datafromDB;
                oSheetold.get_Range(cell + (si + 11).ToString(), cell + (si + 11).ToString()).Font.Bold = 0;
               /* if ((datafromDB.Length > 50) && (l == 2))
                {
                    oSheetold.get_Range("A" + (si + 11).ToString(), "A" + (si + 11).ToString()).Font.Size = 8;
                }*/

                if (l > 1)
                {
                    oSheetold.get_Range(cell + (si + 11).ToString(), cell + (si + l + 11 -2).ToString()).Merge();
                    nextpos = si + l - 2;
                   
                }
                if (cell != "J" && cell != "K")
                {
                    if (cell == "F")
                    {
                        oSheetold.get_Range("G" + (si + 11).ToString(), "G" + (si + 11).ToString()).Value = ap.StartTime.Hour + ":" + ap.StartTime.Minute;
                        oSheetold.get_Range("G" + (si + 11).ToString(), "G" + (si + 11).ToString()).Font.Bold = 0;
                        oSheetold.get_Range("G" + (si + 11).ToString(), "G" + (si + 11).ToString()).Font.Size = 10;
                    }
                    else
                    {
                        oSheetold.get_Range("A" + (si + 11).ToString(), "A" + (si + 11).ToString()).Value = ap.StartTime.Hour + ":" + ap.StartTime.Minute;
                        oSheetold.get_Range("A" + (si + 11).ToString(), "A" + (si + 11).ToString()).Font.Bold = 0;
                        oSheetold.get_Range("A" + (si + 11).ToString(), "A" + (si + 11).ToString()).Font.Size = 10;
                    }
                }
                else 
                {
                    if (cell == "J")
                    {
                        oSheetold.get_Range("I" + (si + 11).ToString(), "I" + (si + 11).ToString()).Value = ap.StartTime.Hour + ":" + ap.StartTime.Minute;
                        oSheetold.get_Range("I" + (si + 11).ToString(), "I" + (si + 11).ToString()).Font.Bold = 0;
                        oSheetold.get_Range("I" + (si + 11).ToString(), "I" + (si + 11).ToString()).Font.Size = 10;
                    }
                    else
                    {
                        oSheetold.get_Range("L" + (si + 11).ToString(), "L" + (si + 11).ToString()).Value = ap.StartTime.Hour + ":" + ap.StartTime.Minute;
                        oSheetold.get_Range("L" + (si + 11).ToString(), "L" + (si + 11).ToString()).Font.Bold = 0;
                        oSheetold.get_Range("L" + (si + 11).ToString(), "L" + (si + 11).ToString()).Font.Size = 10;
                    }
                }
                laststart = si;
                lastlength = l;
                i++;

            }

            // }
            /*      for (int i = 0; i < dt.Items.Count - 1; i++)
            {
                DataRowView li = (DataRowView)dt.Items[i];
                oSheetold.get_Range("A" + (i + 2).ToString(), "A" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(0).ToString();
                oSheetold.get_Range("B" + (i + 2).ToString(), "B" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(1).ToString();
                oSheetold.get_Range("C" + (i + 2).ToString(), "C" + (i + 2).ToString()).Value = li.Row.ItemArray.GetValue(2).ToString();
            }*/
            myCIintl = new CultureInfo("en-US");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;

            oWBold.SaveAs(filename);
            oXLold.Quit();
            oSheetold = null;
            oWBold = null;
            oXLold = null;

            myCIintl = new CultureInfo("lt-LT");
            System.Threading.Thread.CurrentThread.CurrentCulture = myCIintl;
        }

        private static long getCellLength(DateTime dateTime, DateTime dateTime_2)
        {
            int rez = 0;
            long secs = 0;
            if (((dateTime_2.Hour >= 6)&&(dateTime.Hour >= 6))||((dateTime_2.Hour < 6)&&(dateTime.Hour < 6)))
            {
                secs = (((dateTime_2.Ticks - dateTime.Ticks) / 600000000) / 15) + 1;
            }
            else
            {
                dateTime_2 = dateTime_2 + TimeSpan.FromDays(1);
                secs = (((dateTime_2.Ticks - dateTime.Ticks) / 600000000) / 15) + 1;
            }
            return secs;
        }

        private static long getCell(DateTime dateTime)
        {
            long secs = 0;
            if (dateTime.Hour >= 6)
            {
                DateTime dateTime_2 = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, 6, 0, 0);
                secs = (((dateTime.Ticks - dateTime_2.Ticks) / 600000000) / 15) + 1;
            }
            else
            {
                DateTime dateTime_2 = dateTime + TimeSpan.FromDays(1);
                dateTime = new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, 6, 0, 0);
                secs = (((dateTime_2.Ticks - dateTime.Ticks) / 600000000) / 15) + 1;
            }
                return secs;
        }

        private static void exportToExcelProgramaOld(string filename, DateTime dateTime)
        {
            throw new NotImplementedException();
        }
    }
}
